Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am attempting to get the % change from one month to the next. I can do this in a straight table using the above function but when I translate this to a line chart, it does not work because the time dimension now occurs and is sorted before the other dimension. I have attached an incredibly small example that will illustrate what I am attempting. I appreciate any and all feedback!
its annoying but i think you need a data model solution to have Qlik do that properly. I think i've hit this before.
I modified your script to store prior period values in a new field called [Quantity-prior period] . Once you do that the expression is dead easy: sum(Quantity) / sum([Quantity-prior period]).
Hope it helps
Temp:
load * inline [
Item, Year-Month, Quantity
Apple, 2015-1, 1000
Apple, 2015-2, 1200
Apple, 2015-3, 1400
Banana, 2015-1, 1000
Banana, 2015-2, 1000
Banana, 2015-3, 1000
Peach, 2015-1, 1000
Peach, 2015-2, 800
Peach, 2015-3, 600];
NoConcatenate
Data:
Load
*,
AutoNumber(Item&[Year-Month],'ItemMonthKey') as ItemMonthKey
Resident Temp;
left join (Data)
Load
[Quantity-prior period],
AutoNumber(Item&[Year-Month],'ItemMonthKey') as ItemMonthKey;
Load
Item,
date(AddMonths( date#([Year-Month],'YYYY-M'),1),'YYYY-M') as [Year-Month],
Quantity as [Quantity-prior period]
Resident Temp;
drop table Temp;