topic Re: Cumulative Rolling Months in App Development

Cumulative Rolling Months

alex-wb — Fri, 12 Nov 2021 18:08:27 GMT

Hi All,

I am looking to create a 24 month reporting period where the date behind each month is the sum of the current month and the preceding 11 month. This 24 month period will roll each month, with the last month dropping off. So for example, month one is October-2018 and is backed by Nov 2017- October 2018 data, and September 2018 is back by August 2017- October 2018 data and so on so forth.

I have managed to get the rolling period working for the most recent 12 period. However, I cant get rid of the rest of the axis, and also I would prefer to show it like the second screenshot attachment where I have a current RYTD(first 1-12 months) vs the pervious RYTD(13-24 months).

Attached is a sample of data to show how the file looks that I have used. It's completely fictitious. Where I have order Id it would usually be a distinct code but I have replaced it with a single letter that is duplicated.

My data load can be seen in the third screen shot.

The code I have so far which has generated the first screenshot is as follows:

RangeSum(Above(Aggr(count(OrderNo),RMonthYear),0,12))*AVG({< Day_Ordered = {"$(='>' & Date(AddMonths(Max(Day_Ordered),-12))& '<=' & Date(AddMonths(Max(Day_Ordered),0)))"}>}1)

Thanks for the help, I've been stuck on this for ages.

Re: Cumulative Rolling Months

anthonyj — Mon, 15 Nov 2021 01:31:00 GMT

Hi @alex-wb ,

Firstly, Thanks for the great question and for teaching me something new. I've come across this same issue of allocating previous months' aggregations against a specific month in a visualisation and you provided some great code to be able to accomplish that.

According to your excel you wanted a comparison of the months for the previous and current. I think this poses a little problem with the above function because it's only going to look at 1 to 12 instead of different months and years when aggregating.

You may have thought of this already or potentially not wanted to add any code to the data load but my solution is to create a reference table that allocates a reporting year and month against your RMonthYear.

[Data]:
LOAD
[OrderNo],
[Ordered_Day] as [Day_Ordered],
monthstart([Ordered_Day],0) as RMonthYear,
month([Ordered_Day]) as RMonth
FROM [lib://AttachedFiles/Rolling Data.xlsx]
(ooxml, embedded labels, table is Data);

//This section loads a distinct list of the RMonthYear and loops through the dates 12 times, each time allocating 1 month further into the future creating an association with that RMonthYear and the next 12 months.

for i = 0 to 11
MasterCalendar:
Load
month(monthstart(RMonthYear, $(i))) as MasterDateMonth,
monthstart(RMonthYear, $(i)) as MasterDates,
RMonthYear
;

Load Distinct
RMonthYear
Resident Data;
next i;

In your Bar chart you use:

Dimension = MasterDateMonth

Previous RYTD = Count({$<MasterDates={">=$(=addmonths(max(RMonthYear),-23))<=$(=addmonths(max(RMonthYear),-12))"}>}OrderNo)

Current RYTD = Count({$<MasterDates={">=$(=addmonths(max(RMonthYear),-11))<=$(=max(RMonthYear))"}>}OrderNo)

I'd still love to see if someone could do this in set analysis alone but in the mean time I hope this helps.

Thanks

Anthony

Re: Cumulative Rolling Months

vinieme12 — Mon, 15 Nov 2021 10:59:14 GMT

There is no need to load the same data for each month, simply create an AsOf table and associate the preceding month dates

Order:
LOAD OrderNo,
Ordered_Day,
MonthStart(Date(Ordered_Day,'MMM-YYYY')) as MonthStartDate
,Month(Date(Ordered_Day,'MMM-YYYY')) as ActualMonth
FROM
[D:\QlikCommunity\Rolling Data.xlsx]
(ooxml, embedded labels, table is Data);

temp:

Load
date(FieldValue('MonthStartDate',IterNo())) as RollingMonthDate
,recno() as r
AutoGenerate 12
WHILE len(fieldvalue('MonthStartDate',iterno()));

NoConcatenate
AsOf:
Load
*
,Month(RollingMonthDate) as RollingMonth
,monthstart(RollingMonthDate,1-r) as MonthStartDate
Resident temp;
Drop table temp;

Dimension

RollingMonth

expressions

Count({$<RollingMonthDate={">=$(=Monthstart(max(MonthStartDate),-23))<=$(=Monthstart(max(MonthStartDate),-12))"}>}OrderNo)

Count({$<RollingMonthDate={">=$(=Monthstart(max(MonthStartDate),-11))<=$(=Monthstart(max(MonthStartDate)))"}>}OrderNo)

Re: Cumulative Rolling Months

alex-wb — Mon, 15 Nov 2021 15:10:14 GMT

Hi @anthonyj and @vinieme12 , thank you both very much for your response, both solutions work :).

@anthonyj I have seen a version of it that doesn't require you to do it through the data load script, but this was with data that had already been aggregated in SQL to a month level, so you would need to do it somewhere separately if you wished to retain individual order codes.

@vinieme12 Thanks for the Asoftable code, can I ask how it works? Also, I guess the two dates could be included in a master calendar?

Once again thank you both very much.

Re: Cumulative Rolling Months

anthonyj — Mon, 15 Nov 2021 21:44:34 GMT

Hi,

Thanks for the questions and discussion and thanks for the updated code @vinieme12., I hadn't seen these kinds of tables used to address this accumulation issue in Qlik so I had a look at some articles on creating AsOf tables including this one from Henric.

https://community.qlik.com/t5/Qlik-Design-Blog/The-As-Of-Table/ba-p/1466130

It's a great tool for the toolbelt of future requirements.

I did make an error in my initial code which resulted in a large table being generated. I should have created the temp table of distinct monthstart dates first and then entered that in the loop. The result is the exact same table as @vinieme12 . 35 distinct values and 420 rows. There may be a difference in CPU load between the two different approaches but other than that, the end result is the same.

temp:
Load Distinct
MonthStartDate
Resident Data;

for i = 0 to 11
MasterCalendar:
Load
month(monthstart(MonthStartDate, $(i))) as RollingMonth,
monthstart(MonthStartDate, $(i)) as RollingMonthDate,
MonthStartDate
Resident temp;
next i;

drop table temp;

Thanks again for the new ideas.

Anthony

Re: Cumulative Rolling Months

alex-wb — Thu, 25 Nov 2021 11:28:12 GMT

@anthonyj @vinieme12 Quick question regarding how to sort the order of the data. The both codes works great, but I can't order the months correctly, how can I do this?

Re: Cumulative Rolling Months

vinieme12 — Thu, 25 Nov 2021 11:54:26 GMT

Sort by expression

=Max(MonthStartDate)

Re: Cumulative Rolling Months

alex-wb — Thu, 25 Nov 2021 12:00:16 GMT

@vinieme12 Thanks for that. The code you have provided

Load
date(FieldValue('MonthStartDate',IterNo())) as RollingMonthDate
,recno() as r
AutoGenerate 12
WHILE len(fieldvalue('MonthStartDate',iterno()));

NoConcatenate
AsOf:
Load
*
,Month(RollingMonthDate) as RollingMonth
,monthstart(RollingMonthDate,1-r) as MonthStartDate
Resident temp;
Drop table temp;

Can you use this for multiple fields in one app? And how does this code work?
Really appreciate the help by the way.

Re: Cumulative Rolling Months

vinieme12 — Thu, 25 Nov 2021 12:10:40 GMT

Fieldvalue() works on the field which is stored as a list of unique values in-memory instead of the table rows which is why it is faster.

Len(field value) returns the number of unique values in the field.

The while loop simply loops thru the unique values from and Autotgenerate 11 creates 11 rows for each unique value.

Recno() is simply a record count

Re: Cumulative Rolling Months

vinieme12 — Thu, 25 Nov 2021 12:13:19 GMT

Edit and run script without the nested functions and see yourself what each nested function does

Re: Cumulative Rolling Months

alex-wb — Thu, 25 Nov 2021 12:29:01 GMT

@vinieme12 What are the nested functions? I guess if I run the each line individually in the debug script that will work as well?