topic Re: YoY (Year over year growth) by month in QlikView

YoY (Year over year growth) by month

Thu, 31 Dec 2015 07:09:19 GMT

I'm trying to accomplish calculating and visualizing YoY growth. I can get the growth from the previous year to the max year (eg 2013 -> 2014, bottom graph) with this:

=((sum({$<Year = {$(=max(Year))}>}Sales) - sum({$<Year ={$(=max(Year)-1)}>}Sales))/ sum({$<Year = {$(=max(Year)-1)}>}Sales)) * 100

...but what would be the function to get it for all the years in the past (here: 2011 -> 2012 & 2012 -> 2013)?

Re: YoY (Year over year growth) by month

sunny_talwar — Thu, 31 Dec 2015 10:50:07 GMT

It is difficult to give any recommendation without looking at data, but may be add Year as a dimension and use above function to achieve what you are looking for.

Re: YoY (Year over year growth) by month

lironbaram — Thu, 31 Dec 2015 10:58:25 GMT

hi basically you can create more expressions like

=((sum({$<Year = {$(=max(Year)-1)}>}Sales) - sum({$<Year ={$(=max(Year)-2)}>}Sales))/ sum({$<Year = {$(=max(Year)-2)}>}Sales)) * 100

and so on .

for the number of years you want to display

Re: YoY (Year over year growth) by month

Anonymous — Thu, 31 Dec 2015 11:15:11 GMT

addyears() may be an option, like:

addyear(max(YearField),0) - Max Year

addyear(max(YearField),-1) - Last Year

addyear(max(YearField),-2) - Last to Last Year

and so on...

Re: YoY (Year over year growth) by month

Thu, 31 Dec 2015 11:18:11 GMT

Hi,

May be you can try as below three expression with 2014-2013, 2013-2012, 2012-2011 as labels accordingly for below expression:

=((sum({$<Year = {$(=max(Year))}>}Sales) - sum({$<Year ={$(=max(Year)-1)}>}Sales))/ sum({$<Year = {$(=max(Year)-1)}>}Sales)) * 100

=((sum({$<Year = {$(=max(Year)-1)}>}Sales) - sum({$<Year ={$(=max(Year)-2)}>}Sales))/ sum({$<Year = {$(=max(Year)-2)}>}Sales)) * 100

=((sum({$<Year = {$(=max(Year)-2)}>}Sales) - sum({$<Year ={$(=max(Year)-3)}>}Sales))/ sum({$<Year = {$(=max(Year)-3)}>}Sales)) * 100

Re: YoY (Year over year growth) by month

Thu, 31 Dec 2015 12:39:46 GMT

Thanks so much Sunny, Liron, Balraj and Devanand for your quick replys.

So as I understand you all propose to build a function on year by year basis... So there is no generalizable solution, command etc that would run the same calculation across all years no matter how many years?

In Tableau this is accomplished with Quick Table Calculations

Is there something similar in Qlik?

Re: YoY (Year over year growth) by month

sunny_talwar — Thu, 31 Dec 2015 13:12:41 GMT

I think there is a way, but need to be tested on your data. Is it possible to provide a sample?

Re: YoY (Year over year growth) by month

Thu, 31 Dec 2015 13:39:10 GMT

Sure. The sample data set can be found from here: https://community.tableau.com/servlet/JiveServlet/download/364331-59869/Sample%20-%20Superstore%20Subset%20(Excel).xlsx

Re: YoY (Year over year growth) by month

sunny_talwar — Thu, 31 Dec 2015 14:09:30 GMT

Here is how you can do it.

Step 1: Sort your data in the script

This is what I believe you data will be sorted:

Year, Month

2010, Jan

2010, Feb

2010, Mar

2010, Apr

2011, Jan

2011, Feb

You need to change the sort order to this:

Year, Month

2010, Jan

2011, Jan

2012, Jan

2013, Jan

2010, Feb

2011, Feb

Sample Script:

Table:

LOAD Date,

Year(Date) as Year,

Month(Date) as Month,

MonthName(Date) as MonthYear,

Ceil(Rand() * 100000) as Sales;

LOAD Date(MakeDate(2010, 12, 31) + RecNo()) as Date

AutoGenerate (Today() - MakeDate(2010, 12, 31));

FinalTable:

NoConcatenate

LOAD *

Resident Table

Order By Month, Year; // Needed sorting is done in a resident load

DROP Table Table;

On the front end create a line chart with Month and Year as dimension and use this as your expression:

=Aggr(((Sum(Sales)/Above(Sum(Sales))) - 1) * 100, Month, Year)

Again note, this will only work if you have correct sorting.

Re: YoY (Year over year growth) by month

settu_periasamy — Thu, 31 Dec 2015 14:10:10 GMT

May be this?

Put your 'Year' Field also into your dimension and try to create your single expression like

=Sum(Sales)/Above(Sum(Sales))-1

check the sample.. may be helps

Re: YoY (Year over year growth) by month

sunny_talwar — Thu, 31 Dec 2015 15:21:40 GMT

Script:

Table:

LOAD *,

Year([Order Date]) as Year,

Month([Order Date]) as Month,

MonthName([Order Date]) as MonthYear;

LOAD [Row ID],

[Order Priority],

Discount,

[Unit Price],

[Shipping Cost],

[Customer ID],

[Customer Name],

[Ship Mode],

[Customer Segment],

[Product Category],

[Product Sub-Category],

[Product Container],

[Product Name],

[Product Base Margin],

Region,

[State or Province],

City,

[Postal Code],

[Order Date],

[Ship Date],

Profit,

[Quantity ordered new],

Sales,

[Order ID]

FROM

[Sample - Superstore Subset (Excel).xlsx]

(ooxml, embedded labels, table is Orders);

FinalTable:

NoConcatenate

LOAD *

Resident Table

Order By Month, Year;

DROP Table Table;

Expression: =Aggr(((Sum(Sales)/Above(Sum(Sales))) - 1) * 100, Year, Month)

Re: YoY (Year over year growth) by month

Fri, 01 Jan 2016 10:16:50 GMT

Thank you Sonny and Settu for your thoughts, I'm afraid something is not going right in the solutions.

I would need to show a line per year with the monthly change (for example in February (helmi) the change from 2012 => 2013 is about 67% like shown by the yellow line in the lower graph below).

The previous proposals (for example by Devanand) accomplish this, but in that solution a separate line of code is needed for each year. I was looking for a "one liner" to accomplish the same.

The correct graph will look like this:

Any idea in how to get the lower graph with out making a script for each individual line in the graph?

Re: YoY (Year over year growth) by month

sunny_talwar — Fri, 01 Jan 2016 19:36:52 GMT

Would you be able to share the qvw file you are working with, it seems the data you shared is giving different charts then the one with your data. It would be difficult to verify why the results are different because both will give different numbers. Can you share your qvw or the data you are using to create the two charts?

Re: YoY (Year over year growth) by month

Fri, 01 Jan 2016 22:43:23 GMT

Good idea

Re: YoY (Year over year growth) by month

HirisH_V7 — Sat, 02 Jan 2016 13:21:28 GMT

Hi ,

Check this ,

I hope this what you required,

Data:

LOAD * INLINE [

Month, Year, Sales

Jan, 2012, 10

Feb, 2012, 20

Mar, 2012, 19

Apr, 2012, 40

Jan, 2013, 35

Feb, 2013, 31

Mar, 2013, 50

Apr, 2013, 45

Jan, 2014, 50

Feb, 2014, 47

Mar, 2014, 70

Apr, 2014, 65

Jan, 2015, 70

Feb, 2015, 65

Mar, 2015, 90

Apr, 2015, 83

];

Output:

HTH,

-Hirish

Re: YoY (Year over year growth) by month

settu_periasamy — Sat, 02 Jan 2016 14:48:44 GMT

Hi Elina,

As already mentioned by sunindia‌, and me, One expression will give your expected resut.

i.e =(sum(Sales)/Above(sum(Sales))-1)

(format should be Fixed to : 2 and Checked the Shown in Percent (%) in Number Tab

Dimensions: Month and Year

Sort : Month and Year as Ascending

Check the Attachment...

Re: YoY (Year over year growth) by month

Kushal_Chawda — Sat, 02 Jan 2016 15:19:53 GMT

as per suggested by sunindia‌ & settu_periasamy‌ it's working fine but this is alternative to understand the expression like

(current-previous)/previous format

=((sum(Sales) - above(sum(Sales)))/ above(sum(Sales)))

Re: YoY (Year over year growth) by month

sunny_talwar — Sat, 02 Jan 2016 15:29:16 GMT

Script change:

Sales:

LOAD [Row ID],

subfield([Order ID],'-',1) as [Distribution Center],

[Order ID],

Year([Order Date]) as YearTemp,

QuarterName([Order Date]) as Quarter,

Month([Order Date]) as Month,

Num(Month([Order Date])) as NumMonthTemp,

[Order Date],

[Ship Date],

[Ship Mode],

[Customer ID],

[Customer Name],

Segment,

City,

State,

Country,

[Postal Code],

Market,

Region,

[Product ID],

Category,

[Sub-Category],

[Product Name],

Sales,

Quantity,

Discount,

Profit,

[Shipping Cost],

[Order Priority]

FROM

YoY.xlsx

(ooxml, embedded labels, table is Sheet1);

FinalTable:

NoConcatenate

LOAD *,

NumMonthTemp as NumMonth,

YearTemp as Year

Resident Sales

Order By NumMonthTemp, YearTemp;

DROP Table Sales;

Line Chart:

Dimesnions

1) Month

2) Year

Expression: =Aggr(((Sum(Sales)/Above(Sum(Sales))) - 1) * 100, NumMonth, Year)

Re: YoY (Year over year growth) by month

Peter_Cammaert — Sat, 02 Jan 2016 16:00:03 GMT

Works like a charm. The simplest solution, and no script changes required. You only need to apply settu's expression on your own data to get the 66.3% difference between Feb 2013 and Feb 2012.

See Re: YoY (Year over year growth) by month

Re: YoY (Year over year growth) by month

Anonymous — Sat, 02 Jan 2016 17:00:46 GMT

It is difficult to give any recommendation without looking at data, but may be add Year as a dimension and use above function to achieve what you are looking for.