Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
2016 Nov: 176,475 (should be: 181,875)
2017 Dec: 174,003 (should be: 176,403)
@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.
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;
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?
Excluding rx_otc and customer from key changed the numbers but they are still not correct:
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?
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
Can I suggest you an alternative way of doing this instead of doing the accumulation in the script?
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
On the front end create a bar chart like this for rolling 3 months
Dimension
AsOfReportingPeriod
Expression
=Sum({<Num = {'<-3'}>} Value)
Image
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?
Also, is there anyway to calculate YTD and rolling totals in the script still?
@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...
Sorting AsOfReportingPeriod numerically doesn't reverse the order, while sorting the measure numerically leads to this?
What is unusual about those two rows? They have different dimensionality, and thus are on two separate rows.