Calculating Diff in Pivot Table

[jianxiu]

Hi,

I have a pivot table with metric fields in “Row” format as “Values” and a date field as “Columns”.
Sample output would be something like below but without the {Show different between the 2 dates}. I would like to see if there’s a way to have {Show different between the 2 dates} created. In the underlying dataset, metrics are stored in different fields and each date is stored as a row for the record. “Date1” and “Date2” values are coming from user input.

Name | Metrics | Date1 | Date2 | {Show different between the 2 dates}
John Doe | % of A | 5% | 6% | {+1%}
| % of B | 3% | 6% | {+3%}
| % of C | 10% | 5% | {-5%}
| % of D | 30% | 60% | {+30%}
| % of E | 3% | 6% | {+3%}
Mary Smith | % of A | 5% | 6% | {+1%}
| % of B | 3% | 6% | {+3%}
| % of C | 10% | 5% | {-5%}
| % of D | 30% | 60% | {+30%}
| % of E | 3% | 6% | {+3%}

Thanks!

[robdhondt]

Hi @jianxiu -

Here is one approach. The output is slightly different than what you are looking for.

1. Create a calculated field for the percent of metric.

c_per_of_metric

sum(Value)/sumOver(sum(Value),[Metric,Date])

2. Create a calculated field for previous month’s c_per_of_metric

windowSum
	(
	     {c_per_of_metric}, 
	     [Date ASC],
	     1,
              0,[Name,Metric]
	) 
	-
	{c_per_of_metric}

3. Create a calculated field for the percent difference.

c_percent_difference

({c_per_of_metric}-{c_previous_per_of_metric})/{c_previous_per_of_metric}

You’ll get a visual that allows you to compare the two dates but also more than 2.