Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

bharath_kumar1
New Contributor II

YoY Percent Change in Straight table

Hi,

Is it possible to get Year-over-Year Comparison based on quarter by keeping Year as dimension in straight table as below example:

YoY.JPG

For example : 2012 Q1 Sales is compared with 2011 Q1 and percent difference is populated in 2012 Q1 Record.

Is any workaround in Qlik, to get the same ?

For data:

https://community.tableau.com/servlet/JiveServlet/download/364331-59869/Sample%20-%20Superstore%20Su...

Thanks in advance

Bharath

Tags (1)
1 Solution

Accepted Solutions

Re: YoY Percent Change in Straight table

You can try this

=Sum(Sales)/Aggr(Above(Sum(Sales)), [Quarter of Order Date], [Year of Order Date]) - 1

But if you data is not sorted in the ascending order, you might need to use this (this below expression will only work in QV12 or above)

=Sum(Sales)/Aggr(Above(Sum(Sales)), [Quarter of Order Date], ([Year of Order Date], (NUMERIC))) - 1

Alternatively, you can use The As-Of Table

Capture.PNG

4 Replies

Re: YoY Percent Change in Straight table

You can try this

=Sum(Sales)/Aggr(Above(Sum(Sales)), [Quarter of Order Date], [Year of Order Date]) - 1

But if you data is not sorted in the ascending order, you might need to use this (this below expression will only work in QV12 or above)

=Sum(Sales)/Aggr(Above(Sum(Sales)), [Quarter of Order Date], ([Year of Order Date], (NUMERIC))) - 1

Alternatively, you can use The As-Of Table

Capture.PNG

bharath_kumar1
New Contributor II

Re: YoY Percent Change in Straight table

Thanks for your reply@ Sunny Talwar...

It works perfect !

But just a small doubt : By removing NUMERIC from expression

Sorting is not happening correctly, even though my Year field in table is sorted ascending.

Can you help me with the cause of this, why it is so:

YoY doubt.JPG

Thanks,

Bharath

Re: YoY Percent Change in Straight table

That is because of the use of Aggr() function... Aggr() over rides the sorting from the chart and use the sorting from the load order

bharath_kumar1
New Contributor II

Re: YoY Percent Change in Straight table

oh ok !!!

Thanks for your guidance.

Community Browser