Year On Year / Month On Month

[#1]

I would like to create a Year on Year chart for sales data. Yes I know, there are twenty posts on this topic already but I still have not found a solution for my problem. [:S]

I have [year, month, value] columns in my data. There are 24 months worth of sales data. And I want to show the ( value in a month x) as percentage of (value in a month x - 12). This for 12 latest months.

Basically I want to duplicate a pivot table feature of MS Excel. Value Field Settings ->Show values as -> % Difference From, Base Field year, Base Item (previous). I am attaching an example below.

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/674/0028.YoYexample.xlsx:550:0]

I have played with the Time Chart Wisard and it does approximately what I need but only for the latest month. Whereas I need 12 last months. For some reason I am not allowed to attach it here.

The dates in my real data can be put in any any format: timestamp, yyyy-mm-dd, yyyy, mm, or any combination.

[fernandotoledo]

See the example that comes installed with qlikview called template sampler. There they use a chart function called RANGESUM() that allows you to make the kind of calculations you need.

[#3]

Thanks, Fernando!

This is what I came up with: sum(my_value) / rangesum( above( TOTAL sum(my_value),12,1) ) ) . It works for one-dimensional time series chart.

But as soon as I add another dimension this does not work as expected. It seems the problem lays with the underlying result table format. It is a simple table with all dimension being vertical.

So say I have two distinct values in the second dimension, I get my base value from 6 month before not from 12 month before ( 12/2 = 6 rows above). When a pivot table is used for the representation the formula works fine. Pivot works fine because I can make the second dimension to be horisontal.

I am only starting to learn about the dimensions etc. so it would be helpful if somebody could give me a quick hand here.

Speaking in terms of excel I would like to add series to the above example chart of mine.

[#4]

Here I have another version: sum(TPV_USD) / rangesum( above( sum( TPV_USD),12,1) ) and I had to set the sort order by "second_dimension", "time(first) dimension". Now the calculation of the ratio seems to work fine in a straight table, but I get "No data to display" in a (line) chart. Can this be related to the division by 0? Anyways how to get around it?

[#5]

Hi, this is me again, egor_qlikview. I have changed my nick to something a bit more German

Here I have the data and example chart for the two dimensional YoY chart. (YearMonth, SalesDept)

[#6]

And now the example of where it goes wrong in Qlikview

[#7 · Accepted solution]

So, almost a year later I want to share the solution to the problem with the community.

Instead of transforming the data we can add a help table so we can leverage Qlikview built in dimension handling. This gives the optimal balance between the load time, complexity and cpu load at runtime.

In a nutshell using a product join we give every row an alias with the new date stamp 12 month into the future. Actual data size stays the same. Then in Qlikview charts we use "if" statement to separate between current values and values that "arrived from the past" due to the aliasing.

Here is an example of an expression to chart the difference in sales.

(sum(  if(year_type='current',SalesVolume,0)) - sum(  if(year_type='previous',SalesVolume,0)))

This way any other dimension that is present in the sales table can be used in a usual way. Drill down or circular groups can be used too without any changes to the expression or load script.

The data has at least three column: SalesVolume, sales_date, sales_month (month was originally added to save runtime cpu time for aggregation)

sales:
LOAD SalesVolume, sales_date, sales_month, dim1, dim2, dimEtc from sales.qvd;
// scanning the data for the date boundaries
dropme:
LOAD date(od_min + iterno() - 1) as gen_date, od_max WHILE iterno() <= od_max - od_min + 1; 
LOAD min(sale_date) as od_min, max(sale_date) as od_max RESIDENT sales GROUP BY 1; 
// this is the table that serves to alias the sales_month through product join with the original data table
yoy_month:
LOAD
 'current' as "year_type"
 , year(gen_date) & '-' & num(month(gen_date),'00') as "sales_month"
 , year(gen_date) & '-' & num(month(gen_date),'00') as "sales_YoY_month"    
resident dropme
;
LOAD
 'previous' as "year_type"
 , year(gen_date) & '-' & num(month(gen_date),'00') as "sales_month"
, year(gen_date)+1 & '-' & num(month(gen_date),'00') as "sales_YoY_month"        
resident dropme
where AddMonths(gen_date,12) <= od_max
;
drop table dropme;

After we have this table we just have to use "sales_YoY_month" for the time dimension in YoY charts.

[#8]

Hello all,

Is it possible to show YoY% even when QtrYear is selected?

QtrYear will show nothing but 2014/Q1, 2014Q2.

If I select 2014/Q1 from QtrYear field, YoY% should show numbers for 2014Q1 vs 2013Q1.

Is this possible?

Thanks in advance

Vidhya