Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
alex-wb
Contributor III
Contributor III

Cumulative Rolling Months

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. 

 

alexwb_1-1636739926584.png

alexwb_2-1636739942234.png

alexwb_3-1636740400723.png

 

 

 

3 Solutions

Accepted Solutions
anthonyj
Creator III
Creator III

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) 

anthonyj_0-1636939717273.png

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

View solution in original post

vinieme12
Champion II
Champion II

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)

View solution in original post

vinieme12
Champion II
Champion II

Sort by expression

=Max(MonthStartDate)

View solution in original post

11 Replies
anthonyj
Creator III
Creator III

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) 

anthonyj_0-1636939717273.png

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

View solution in original post

vinieme12
Champion II
Champion II

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)

View solution in original post

alex-wb
Contributor III
Contributor III
Author

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
Creator III
Creator III

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
Contributor III
Contributor III
Author

@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
Champion II
Champion II

Sort by expression

=Max(MonthStartDate)

View solution in original post

alex-wb
Contributor III
Contributor III
Author

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

Pietsch15
Contributor
Contributor

Means it takes the previous months values too. Ex; if I select Feb month means it shows the cumulative sum of Jan+Feb. Similarly for Mar = JanFebMar. Here in this calculation right now I using Order Date but I want to calculate the same formula based on Fiscal month .

 

My Balance Now

vinieme12
Champion II
Champion II

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