# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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

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?

Laura

Labels (3)

26 Replies
MVP

## 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.
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?

MVP

## 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?
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.

MVP

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

This is for YTD

```Table:
Hash128(Brand&'|'&sku&'|'&channel&'|'&rx_otc&'|'&customer&'|'&version) as Key,
Year(reporting_period) as reporting_year;
sku,
channel,
rx_otc,
customer,
version,
Date#(reporting_period, 'YYYYMM') as reporting_period,
Value,
Value_1
FROM
(ooxml, embedded labels, table is Sheet2);

FinalTable:
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;```
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?

MVP

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

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

New Contributor

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

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

MVP

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

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?