topic Re: Year on Year Growth in QlikView

Year on Year Growth

Anonymous — Fri, 22 May 2015 11:03:34 GMT

Hi,

I need to get year on year growth for sales, cost and profit. Attached a sample file. Please help on the methodology.

The formula for yoy growth is (current year-prev year)/prev year, I don't know how to get it right in QV. Sum of sales by year and then using the formula.

Thanks

Sunny

Re: Year on Year Growth

timanshu — Fri, 22 May 2015 11:14:54 GMT

Write this in first expression:

Sum(Profit)

write this in second expression :

(Sum(profit) /sum(above(column(1)))-1

Order the chart by Year Ascending.

Do similarly for sales, cost etc...

Re: Year on Year Growth

robert_mika — Fri, 22 May 2015 11:20:26 GMT

Try:

=(sum(Sales)-Above(sum(Sales)))/Above(sum(Sales))

used this expression for Profit and Cost

Re: Year on Year Growth

Anonymous — Fri, 22 May 2015 11:52:02 GMT

Hi Robert, dont I have to sort the data by year before using this formula

Re: Year on Year Growth

robert_mika — Fri, 22 May 2015 12:24:57 GMT

No as the default sort is numeric ascending

Re: Year on Year Growth

vvvvvvizard — Fri, 22 May 2015 12:28:10 GMT

what Robert said

Re: Year on Year Growth

Fri, 22 May 2015 12:41:38 GMT

Hi Sunny,

The solution can be done in script or GUI. Below is a sample code.

Detail:

LOAD Date,

Sales,

Cost,

Profit

FROM

[Yoy growth.xlsx]

(ooxml, embedded labels, table is Sheet1);

YoY_Temp:

LOAD Date,

SUM(Sales) AS Sales

RESIDENT Detail

GROUP BY Date;

YoY:

LOAD Date,

Sales,

(Sales-PREVIOUS(Sales))/PREVIOUS(Sales) AS YOY_Sales

RESIDENT YoY_Temp;

Thanks,

Re: Year on Year Growth

Anonymous — Fri, 22 May 2015 13:50:32 GMT

Thanks for the response guys,

Just confused between Robert's and Umesh's response.

Robert is using Above function where Umesh is using Previous, what's the difference and which is the most apt?

Thanks

Re: Year on Year Growth

robert_mika — Fri, 22 May 2015 13:57:56 GMT

Previous works in load statement - Back end

Above in Front End - Charts

Re: Year on Year Growth

Anonymous — Fri, 22 May 2015 14:18:12 GMT

Thanks Robert, appreciate the response