Calculating Diff in Pivot Table

Q&A
, ,
1

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!

2

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])
  1. 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}
  1. 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.

image
image1295×716 40.4 KB

2 Likes
3

@robdhondt While appreciating the suggestion with the percent of metric calculation, I still believe there is a much simpler answer to the initial question being posted here. The point I want to make is that even the question contains the notion of percentage, it is not really about percentages at all. To be precise, what is asked for is a calculation which can display the change in any measure, here the measure is about percentages, but it might as well have been change in the numer of fruits, or cars.

Consider the following dataset:

Name,col1,col2
John Doe,3,6
John Doe,10,5
John Doe,30,60
John Doe,47,2
Mary Smith,10,9
Mary Smith,1,11
Mary Smith,12,9
Mary Smith,12,17
Mary Smith,99,88

and this calculation:

change = col2-col1

Displaying this as a table, yields this:

image
image711×755 30.8 KB

4

How to calculate the difference in this scenario col1 and col2 are in one column but its split based on name while using Pivot?

2 Likes