Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
laura_1
New Contributor

Calculating YTD, moving totals with multiple dimensions in script

Hi, 

I have a table that has multiple dimensions for every measure  (reporting period contains 2 years' worth of data):Photo1.PNG

 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):

Capture.PNG

How can I adjust the script to get around this issue? 

Thanks very much in advance! 

Laura 

 

 

 

Labels (3)
26 Replies

Re: Calculating YTD, moving totals with multiple dimensions in script

Do you want to still get YTD based on all the dimensions or do you need a ytd based on just Brand? if it is just based on Brand, then you will need to create a separate aggregated table where you will sum(value) based on Brand. If you want to do this for all the dimensions... then you need to order by all the dimensions and need to add if for all the dimensions just like you do it for brand.
laura_1
New Contributor

Re: Calculating YTD, moving totals with multiple dimensions in script

Thank you Sunny! What about YTD formula as it doesn't have an if for Brand, but only for Year? 

Re: Calculating YTD, moving totals with multiple dimensions in script

I think it might be easy if you can share the input data (Excel) and also provide the output you are hoping to see from it?
laura_1
New Contributor

Re: Calculating YTD, moving totals with multiple dimensions in script

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. 

Re: Calculating YTD, moving totals with multiple dimensions in script

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;
laura_1
New Contributor

Re: Calculating YTD, moving totals with multiple dimensions in script

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? 

Re: Calculating YTD, moving totals with multiple dimensions in script

What is wrong with 2016? Looks okay to me?

image.png

The above screenshot is from QlikView, but this should work the same way in Qlik Sense

laura_1
New Contributor

Re: Calculating YTD, moving totals with multiple dimensions in script

These are the results I get:Capture1.PNG

 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:

brandskuchannelrx_otccustomerversionreporting_periodValueValue_1
Brand 1SKU 1Channel 1RxCustomer  1Actual2016111196251

 

Whereas for 2016 October and November there are multiple instances:

brandskuchannelrx_otccustomerversionreporting_periodValueValue_1
Brand 1SKU 1Channel 1RxCustomer  1Actual2016091162289
Brand 1SKU 1Channel 2OTCCustomer 2Actual2016091000100
Brand 1SKU 1Channel 3RxCustomer 3Actual2016092000200

 

Re: Calculating YTD, moving totals with multiple dimensions in script

I think the issue stems from this

image.png

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?