Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
May be this?
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
Hi
Let me check