Calculating YTD, moving totals with multiple dimen... - Page 3 - Qlik Community

laura_1 · ‎2018-12-20

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 · ‎2018-12-20

You really made me do a Qlik Sense sample... I am not sure but the sorting works just right for me...

I am attaching the zipped file which include the .qvf file for you to look at... I am not sure how else I can help.

laura_1 · ‎2018-12-20

Turns out the issue was the field order in the sorting panel - I now changed the order and it works, thanks Sunny!

Is there anything that could be done about the script at all?

sunny_talwar · ‎2018-12-20

I think you are only reading my messages partially. This is the third time I am going to ask you about this

Why are there two rows for 201610 for Brand = Brand1, sku = SKU 1, channel = Channel 3. We can only have a single row for each of the Key and reporting_period... in fact if we don't have a certain combination... even then it is a problem. And that is why I suggest using the The As Of Table. It handles any missing values for you and any duplicate row problem.

laura_1 · ‎2018-12-20

There are two rows because dimensionality is different - rx_otc and customer columns have different values for those two rows, that's why there are two, instead of one.

And I have tried the using a key that includes both rx_otc and customer too and it still didn't work.

sunny_talwar · ‎2018-12-20

@laura_1 wrote:
There are two rows because dimensionality is different - rx_otc and customer columns have different values for those two rows, that's why there are two, instead of one.

Make sense and that is why AsOfTable

@laura_1 wrote:
And I have tried the using a key that includes both rx_otc and customer too and it still didn't work.

Again makes perfect sense and that is why AsOfTable

The alternative if you really want to do this is to create each and every combination of Key (made up of Brand, sku, channel, rx_otc, channel) with each reporting_period. Right now if you look at the top row... it doesn't have a corresponding 201610 because the last row's customer is Customer 4 and Customer 3.... so in order to fix this... you will need to add a new row which will have the same Key, with reporting_period as 201610 and another two which will be the same key with reporting_period 201611 and 201612. Do you want to go this complicated route?

laura_1 · ‎2018-12-21

Thanks for all your help, it does appear to make more sense to do it as an As Of Table. With As Of Table, is it possible to calculate YTD, MAT etc. in the script or front end only? I tried joining the As Of Table with my fact table to make YTD, MAT etc. calculations in script, but it produces wrong results due to the join..

sunny_talwar · ‎2018-12-21

It may not work very well... I am not sure because I have never tried it. I think it is not worth doing it.

Praneeth_Konduru · ‎2021-12-16

Hello Sunny,

I have a similar requirement. I need to calculate YTD hours for each employee working on each Task or WBS element.

Note: Task or WBS element Column contains Task numbers as well as some Text values.

I used the expression you used in my data to calculate YTD hours. Everything works great but for some records, the YTD is just giving me hours for the month selected instead of summing the hours from the beginning of the year till the month selected. (I think that the if condition is failing and picking the else condition which is Sum(Total Hours) which is the MTD value).

I tried using Autonumber(Task) to convert the text values to unique number and used that to form the key, still no luck.

Need your insights and assistance please. See below for the Script I developed.

AGNTemp:
LOAD

"Task or WBS element",
AutoNumber("Task or WBS element") as [Num_Task or WBS element], //newly added
Project,
"Project Description",
"Therapeutic Area",
Resource,
"Resource's First name",
"Resource's Last Name",
"Resource's Email",
"Resource's Job Title",
"Employee type",
"Location name",
"Comment",
Company,
"Cost Center #",
"Cost center name",
// "Month",
// "Start date",
// "End date",
"Total load",
// Date#(Month("End date")& '-' &Year("End date"), 'MMM-YYYY') as [AGN.TimePeriod],
Date#(Left("Month",3)& '-' & 2021, 'MMM-YYYY') as [AGN.TimePeriod],
Resource &'|'& "Resource's First name" & ' ' & "Resource's Last Name" as Employee,
Project & ' - ' & "Project Description" as [AGN.Project Code and Name],
[Cost Center #]& ' - '&"Cost center name" as [Cost Center Code and Name]

FROM [lib://AGN Historical/Actual Hours - 2021.xlsx]
(ooxml, embedded labels, table is OPX2);

AGN_YTD:

Drop Table AGNGrouped;

// for YTD calculation in the script
FinalTable:
LOAD *,
If(Key = Previous(Key) and [AGN.TimePeriod_Year] = Previous([AGN.TimePeriod_Year]), RangeSum(Peek('YTD'), [Total Hours]), [Total Hours]) as YTD
Resident AGN_YTD
Order By Key, [AGN.TimePeriod];

DROP Table AGN_YTD;

Thank You

Sai Praneeth Konduru

Praneeth_Konduru · ‎2021-12-17

Update:

Figured the issue and fixed it.

So the "Comment" field has some text values and blank values which was causing the issue when used in the Key.

Removing the Comment from the key resolved the issue.

Calculating YTD, moving totals with multiple dimensions in script

MAT

moving total

ytd