topic Re: YOY calculation in pivot table in QlikView

YOY calculation in pivot table

Anonymous — Sun, 11 Feb 2018 13:36:36 GMT

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

Re: YOY calculation in pivot table

MK_QSL — Sun, 11 Feb 2018 13:46:00 GMT

Like this?

Re: YOY calculation in pivot table

Anonymous — Sun, 11 Feb 2018 13:56:15 GMT

Thanks manish for your response,

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

Re: YOY calculation in pivot table

MK_QSL — Sun, 11 Feb 2018 14:23:30 GMT

Pivot Table

Dimension

country

product

year

Expression

=sum(revenue)

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

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

Re: YOY calculation in pivot table

Anonymous — Sun, 11 Feb 2018 14:30:07 GMT

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

Regards,

Sonthu

Re: YOY calculation in pivot table

MK_QSL — Sun, 11 Feb 2018 17:56:01 GMT

of course

Use same dimensions as you mentioned !

Expression

1) sum(revenue)

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

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

Re: YOY calculation in pivot table

Anonymous — Sun, 11 Feb 2018 18:58:42 GMT

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?

Re: YOY calculation in pivot table

MK_QSL — Sun, 11 Feb 2018 19:26:52 GMT

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.

Re: YOY calculation in pivot table

Anonymous — Mon, 12 Feb 2018 13:27:39 GMT

hi manish

waiting for your updates.