Calculating YTD, moving totals with multiple dimensions in script

[laura_1]

Hi, 

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! 

Laura 

[sunny_talwar]

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]

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

[sunny_talwar]

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]

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. 

[sunny_talwar]

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]

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? 

[sunny_talwar]

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

[laura_1]

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	sku	channel	rx_otc	customer	version	reporting_period	Value	Value_1
Brand 1	SKU 1	Channel 1	Rx	Customer  1	Actual	201611	1196	251

Whereas for 2016 October and November there are multiple instances:

brand	sku	channel	rx_otc	customer	version	reporting_period	Value	Value_1
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

[sunny_talwar]

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?