YOY calculation in pivot table

[Anonymous]

Hi All,

I would require a help for  calculation of a separate column YOY(year over year)  in a pivot table. i am not able to come up with any solution. Please find the attached File.

The calculation for the column will be ((current year(revenue) - previous year(revenue) )/previous year (revenue))

I have used the below inline statement to mock up the data. the Country, year and product are the dimension in my pivot table.

data:

load * inline

[

country,year,product,revenue

US,2015,cash,6356

US,2015,pgm trading,354

US,2015,Options,847

US,2016,cash,7467

US,2016,pgm trading,355

US,2016,Options,734

US,2017,cash,837

US,2017,pgm trading,4587

US,2017,Options,86

Canada,2015,cash,3476

Canada,2015,pgm trading,4434

Canada,2015,Options,788

Canada,2016,cash,3444

Canada,2016,pgm trading,8588

Canada,2016,Options,897

Canada,2017,cash,7634

Canada,2017,pgm trading,7565

Canada,2017,Options,889

];

thanks in advance

[MK_QSL]

Like this?

[Anonymous]

Thanks manish for your response,

can you please share the  expression as i cannot see it because i have  personal edition installed.

[MK_QSL]

Pivot Table

Dimension

country

product

year

Expression

1)

=sum(revenue)

2)

=If(RowNo()=1,0,Above(sum(revenue)))

3)

(SUM(revenue) - If(RowNo()=1,0,Above(sum(revenue))))/If(RowNo()=1,0,Above(sum(revenue)))

[Anonymous]

Manish, there is a catch here, my second dimension is Year not product. Hope you can help me with that.

Regards,

Sonthu

[MK_QSL · Accepted solution]

of course

Use same dimensions as you mentioned !

Expression

1) sum(revenue)

2)

(sum(revenue)- aggr(above(sum({<year=>}revenue)),country,product,year))

/

aggr(above(sum({<year=>}revenue)),country,product,year)

[Anonymous]

Hi Manish,

thanks for the expression and it seems to have worked for me but i am not able to grasp it.

i have sorted the year in descending and your above formula worked . isn't it wierd? you are using above function

but there is no data above the year 2017, how does it work? can you please give me a short explanation. sorry for bothering you. please see the screenshot of what i did. also please find the file attached in case you want to refer.

Also can we have a calculation for total too?

[MK_QSL]

I am outside so will come back to you tomorrow. Can you close this thread by selecting my answer correct or helpful?

I will come back to you on your query will proper explanation.

[Anonymous]

hi manish

waiting for your updates.