topic Percent change, Year over Year in QlikView

Percent change, Year over Year

Wed, 13 Mar 2013 16:23:35 GMT

I've researched other discussions, and many are similar to my question, but on the advice given, I'm still having trouble.

I am trying to add an expression to show me the year over year change in a sales number.

I am able to accomplish this by using an expression that includes Below() but because of the order that I need my expressions to appear this is not efficient.

I want to use a dimension limit to show me the top 5 largest increases in sales. But to do this I would need my first expression to be that change in sales amount. So I'd like to know if there's a way to accomplish this without using Below()?

I see in other posts people recommend setting a variable equal to the max year and then using this in a set analysis. But I want mine to be dynamic. So if I have data from 2002-2013, and the user highlights from 2007-2009, I want them to see who had the largest increases in sales during that time period.

I've attached an excel file with the table and also showing my formulas.

Re: Percent change, Year over Year

Gysbert_Wassenaar — Wed, 13 Mar 2013 17:13:19 GMT

Calculate the previous years sales in the script and use the new field PYSales in your charts:

T1:

LOAD ConsolidatedTenant,

SalesMall,

Year,

Sales

FROM

[Sales Change Example.xls]

(biff, embedded labels, table is [Sheet1$]);

T2:

NoConcatenate

load *,

if(previous(ConsolidatedTenant&SalesMall)=ConsolidatedTenant&SalesMall,previous(Sales)) as PYSales

resident T1

order by ConsolidatedTenant, SalesMall, Year;

drop Table T1;

If you want you can use a preceding load to calculate the Year-on-Year variance percentage in the script too

Re: Percent change, Year over Year

Wed, 13 Mar 2013 18:28:02 GMT

This seems to work...but seems a little touchy depending on how I display the table.

Seems like if I don't include the year as a dimension the values don't line up. I'll have to double check my order by clause in the script.

I was hoping for a way using set analysis though?

Re: Percent change, Year over Year

somenathroy — Thu, 14 Mar 2013 05:09:17 GMT

Hi,

PFA.

Regards,

Som

Re: Percent change, Year over Year

Thu, 14 Mar 2013 10:28:28 GMT

Thanks, the attachment from somenathroy works using set analysis. However the modification to the script is going to help me with my next step in doing some comp sales analysis.

so thank you both!

Re: Percent change, Year over Year

jleefjcapital — Wed, 02 Nov 2016 20:13:22 GMT

Hello,

I would like to know if you could provide a solution for a similar problem I'm having with my Qlik Sense application. I am trying to create a new field of year-over-year growth rates that I could use and manipulate in the data visualization tool independent of the original field.

Your help on this would be much appreciated.

Thank you,

Jes