YoY (Year over year growth) by month

[#1]

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)? 

[sunny_talwar]

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.

[lironbaram]

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

[Anonymous]

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

[#5]

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

[#6]

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?

[sunny_talwar]

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

[#8]

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

[sunny_talwar]

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.

[settu_periasamy]

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