Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

% Change over user selected year columns

Hi all,

 

First time poster! I'd appreciate any help you can give me with this question...

 

I need to create a dynamic matrix and line chart that shows the percentage change over selected year columns. Example below.

 

There are 10 years in the report and a user can select any combination of years. The selected years need not be contiguous, but are displayed in order (L to R). If a user adds more years, the table recalculates to reflect all possible % changes. If only 1 year is selected you'd see a blank table/chart. I think this means that a date table may not be appropriate as I want the calculation to work over non-contiguous years.

 

The result I'm looking for is exactly the same as if you used a pivot table to show % Difference From (previous).

 

So from this:

 

Category202020222023
A405379338089411576
B263250229064279059
C198641103715044
D9030743171125443
E305931760029115
Grand Total809393638961860237

 

I want this:

 

Category202020222023
A -17%22%
B -13%22%
C -44%36%
D -52%191%
E -42%65%
Grand Total -21%35%

 

And this:

 

 

Happy to provide more info.

 

Many thanks!

 

Chris.

2 Replies