Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Year on Year Growth

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

1 Solution

Accepted Solutions
robert_mika
Master III
Master III

Try:

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

used this expression for Profit and Cost

View solution in original post

9 Replies
timanshu
Creator III
Creator III

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...

robert_mika
Master III
Master III

Try:

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

used this expression for Profit and Cost

Anonymous
Not applicable
Author

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

robert_mika
Master III
Master III

No as the default sort is numeric ascending

vvvvvvizard
Partner - Specialist
Partner - Specialist

what Robert said

Not applicable
Author

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,

Anonymous
Not applicable
Author

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

robert_mika
Master III
Master III

Previous works in load statement - Back end

Above in Front End - Charts

Anonymous
Not applicable
Author

Thanks Robert, appreciate the response