Cumulative Rolling Months

[alex-wb]

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. 

[anthonyj · Accepted solution]

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

[vinieme12 · Accepted solution]

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)

[alex-wb]

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.

[anthonyj]

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

[alex-wb]

@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?

[vinieme12 · Accepted solution]

Sort by expression

=Max(MonthStartDate)

[alex-wb]

 @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.

[vinieme12]

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 

[vinieme12]

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