Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
laura_1
Partner - Contributor III
Partner - Contributor III

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)
28 Replies
laura_1
Partner - Contributor III
Partner - Contributor III
Author

Thanks Sunny, I included rx_otc in the key and it fixed it! 

How could I address that same issue with moving totals? Adjusted formula for 3 month rolling total:

If(brand= Peek('brand', -2) and sku=Peek('sku', -2) and customer=Peek('customer', -2) and
channel=Peek('channel', -2) and rx_otc=Peek('rx_otc', -2), Rangesum(Value, Peek('Value', -1),
Peek('Value', -2))) as Rolling3M

Order by key, brand, sku, customer,rx_otc, year,month;Capture2.PNG

 2016 Nov: 176,475 (should be: 181,875)

2017 Dec: 174,003 (should be: 176,403)

 

sunny_talwar


@laura_1 wrote:

 I included rx_otc in the key and it fixed it! 

You included it or excluded it? Can you share the new script you are using so that we are on the same page.

laura_1
Partner - Contributor III
Partner - Contributor III
Author

I included it, here's the full script I'm using to calcutate YTD, MAT and Rolling 3 month total:

Table_temp:
LOAD
Brand as brand,
sku,
channel,
rx_otc,
customer,
// version,
Date(Date#(reporting_period, 'YYYYMM'), 'DD/MM/YYYY') AS date,
Date(MonthStart(Date(Date#(reporting_period, 'YYYYMM'), 'DD/MM/YYYY')), 'YYYY MMM') as month,
Year(Date(Date#(reporting_period, 'YYYYMM'), 'DD/MM/YYYY')) as year,
Hash128(brand & ' | ' & sku & ' | ' & channel & ' | ' & customer & ' | ' & rx_otc) as key,
Value,
Value1
FROM [lib://Source Data/Table.xlsx]
(ooxml, embedded labels, table is Sheet2);

Table_calc:
LOAD
date,
month,
year,
brand,
sku,
customer,
channel,
rx_otc,
key,
Value,
Value_1,
If(key = Previous(key) and year = Previous(year), RangeSum(Peek('YTD'), Value), Value)
as YTD,
If(brand= Peek('brand', -11) and sku=Peek('sku', -11) and customer=Peek('customer', -11) and
channel=Peek('channel', -11) and rx_otc=Peek('rx_otc', -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))) as MAT,
If(brand= Peek('brand', -2) and sku=Peek('sku', -2) and customer=Peek('customer', -2) and
channel=Peek('channel', -2) and rx_otc=Peek('rx_otc', -2), Rangesum(Value, Peek('Value', -1),
Peek('Value', -2))) as Rolling3M
RESIDENT Table_temp
ORDER BY key, brand, sku, customer, channel, rx_otc, year,month;
DROP TABLE Table_temp;

sunny_talwar

This code fixed your issue with YTD field in 2016? I am still seeing 120378 for 201610 and 176475 for 201611. But these are not the right numbers, or are they?

laura_1
Partner - Contributor III
Partner - Contributor III
Author

Excluding rx_otc and customer from key changed the numbers but they are still not correct:Capture.PNG

 

 

 

 

 

 

 

 

 

October should be 123,378 and November should be 181,875.

As raised earlier, these are the only two months where the same sku is associated with the same month more than once - could that be the issue?

sunny_talwar

Okay, I am not sure how you want to handle this... but now your key is made up of Brand, sku, and channel, right? and I see two rows for 201610

image.png

 

Can I suggest you an alternative way of doing this instead of doing the accumulation in the script?

sunny_talwar

Alternative method using AsOfTable

Script

Table:
LOAD Brand, 
     sku, 
     channel, 
     rx_otc, 
     customer, 
     version, 
     Date#(reporting_period, 'YYYYMM') as reporting_period,
     Value, 
     Value_1,
     Year(Date#(reporting_period, 'YYYYMM')) as reporting_year
FROM
[..\..\Downloads\Table.xlsx]
(ooxml, embedded labels, table is Sheet2);

AsOfTable:
Left Keep (Table)
LOAD DISTINCT reporting_period as AsOfReportingPeriod,
	 reporting_year as AsOfReportingYear,
	 Date(MonthStart(reporting_period, -IterNo() + 1), 'YYYYMM') as reporting_period,
	 -IterNo() + 1 as Num
Resident Table
While -IterNo() + 1 > -12;

On the front end create a bar chart like this for YTD

Dimension

AsOfReportingPeriod

Expression

=Sum(If(AsOfReportingYear = reporting_year, Value))

Image

image.png

On the front end create a bar chart like this for rolling 3 months

Dimension

AsOfReportingPeriod

Expression

=Sum({<Num = {'<-3'}>} Value)

Image

image.png

laura_1
Partner - Contributor III
Partner - Contributor III
Author

Thanks for suggesting the alternative, Sunny,  the dates seem to be in reverse order though? And sorting alphabetically doesn't reverse that - is there something that can be done in the script?

Capture.PNG

 Also, is there anyway to calculate YTD and rolling totals in the script still? 

sunny_talwar


@laura_1 wrote:

Thanks for suggesting the alternative, Sunny,  the dates seem to be in reverse order though? And sorting alphabetically doesn't reverse that - is there something that can be done in the script?

 


Sort them numerically in ascending order


@laura_1 wrote:

 Also, is there anyway to calculate YTD and rolling totals in the script still? 


Not until you can help me understand the two rows for 201610 for our key... 

image.png

laura_1
Partner - Contributor III
Partner - Contributor III
Author

Sorting AsOfReportingPeriod numerically doesn't reverse the order, while sorting the measure numerically leads to this? Capture.PNG

 What is unusual about those two rows? They have different dimensionality, and thus are on two separate rows.