Calculating monthly % change when month is a dimension

[saurabhk18]

I'm sorry I couldn't think of a better title.

I'm working on Qlikview 11.2 and have only four months' experience working on it. Here is my scenario:

I have a data set, a sample of which looks like this (Dec-17 indicates Dec 2017):

Source	Date	Series ID	Index Value
A	Dec-17	A101	100
A	Jan-18	A101	101
A	Feb-18	A101	99

There are many such sources (column 1) each of which has multiple series IDs (column 3). The value of these series IDs (last column) is tracked over time. I have a requirement to track the monthly % change ((value in current month - value in previous month)/value in previous month) * 100. Here is a sample of the expected result:

Source	Series ID	Dec-17	Jan-17	Feb-17
A	A101	0	1	-2

The date, as usual, is a dimension. How do I write the expression such that it picks up the month in the column header to get the current month and subtracts one month from it to get the previous month?

Note: Source, Series ID and Date are dimensions, but I've tabulated them together for ease of understanding.

Thanks in advance!

-Saurabh

[shiveshsingh]

May be this?

[saurabhk18]

Hi Shivesh,

Thanks for your answer. The data I shared was just a sample. There are multiple sources, each of which has multiple series IDs. And we have index values for each series ID since 2014 (so the months will be Jan-14, Feb-14...Mar-18).

Also, I would like to show the data as a pivot table with Source, Series ID and Date as dimensions. The expression will be ((value in current month - value in previous month)/value in previous month) * 100, like the second table I shared:

Source	Series ID	Dec-17	Jan-17	Feb-17
A	A101	0	1	-2

Do you have a solution for this without creating column(1) and column(2) (as you've done in your code) ?

Thanks,

Saurabh

[shiveshsingh]

Hi

Let me check