I have a table that has multiple dimensions for every measure (reporting period contains 2 years' worth of data):
I need to calculate YTD and moving totals (12 and 3 rolling months) in script. I derived additional date fields in the script (year-month, year) and used below formulas which worked with two dimensions (Brand and reporting period):
YTD: If(Peek('Year')=Year, RangeSum(Peek('YTD'), Value), Value)
MAT: If(Brand= Peek('Brand', -11), Rangesum(Value, Peek('Value', -1), Peek('Value', -2), Peek('Value', -3), Peek('Value', -4), Peek('Value', -5), Peek('Value', -6), Peek('Value', -7), Peek('Value', -8), Peek('Value', -9), Peek('Value', -10), Peek('Value', -11)))
Rolling 3 months: If(Brand= Peek('Brand', -2), Rangesum(Value, Peek('Value', -1), Peek('Value', -2)))
Order by Brand,Year, Month;
But due to many dimensions, I now have multiple rows per each date, which is causing YTD (and others) to calculate incorrectly (when date is plotted on the x axis, YTD values are double counted as shown here):
How can I adjust the script to get around this issue?
Thanks very much in advance!
Sure, please see input file attached.
In terms of output, it'd like to calculate YTD for all of these dimensions (so not just brand), so that that on the charts I could filter it by customer, channel, sku, etc.
This is for YTD
Table: LOAD *, Hash128(Brand&'|'&sku&'|'&channel&'|'&rx_otc&'|'&customer&'|'&version) as Key, Year(reporting_period) as reporting_year; LOAD Brand, sku, channel, rx_otc, customer, version, Date#(reporting_period, 'YYYYMM') as reporting_period, Value, Value_1 FROM [..\..\Downloads\Table.xlsx] (ooxml, embedded labels, table is Sheet2); FinalTable: LOAD *, If(Key = Previous(Key) and reporting_year = Previous(reporting_year), RangeSum(Peek('YTD'), Value), Value) as YTD Resident Table Order By Key, reporting_period; DROP Table Table;
Thanks very much Sunny, it's working correctly for data from 2017 and 2018 but not 2016 (same happening with moving totals calculations) - do you happen to know why that might be the case?
What is wrong with 2016? Looks okay to me?
The above screenshot is from QlikView, but this should work the same way in Qlik Sense
These are the results I get:
Cross-checking it with the table in excel it should be:
2016 Sept: 62,636 (correct)
2016 Oct: 123,378 (vs 120,378)
2016 Nov: 181,875 (vs 176,475)
2016 Dec: 233,939 (correct)
I suspect it might be because for all other months same SKU maps to same reporting period only once:
|Brand 1||SKU 1||Channel 1||Rx||Customer 1||Actual||201611||1196||251|
Whereas for 2016 October and November there are multiple instances:
|Brand 1||SKU 1||Channel 1||Rx||Customer 1||Actual||201609||1162||289|
|Brand 1||SKU 1||Channel 2||OTC||Customer 2||Actual||201609||1000||100|
|Brand 1||SKU 1||Channel 3||Rx||Customer 3||Actual||201609||2000||200|
I think the issue stems from this
rx_otc changes from Rx to OTC.... and we loose the YTD.... do you may be not want to include rx_otc and customer within the Key field that we create?