Creating groups of days in order to find past groups

[themanwithnoname]

I need to create groups of days based on the recurring days each month. These are grouped by the 1st, 5th, 10th, 15th, 20th, and 25th of each month. So, they could be grouped from 1-4, 5-9, etc. They are currently dates (YYYY-MM-DD), so I have currently used the following method for the groups. If there is a more efficient way, such as IntervalMatch, I can do that too.

If(Day($1)>= 1 and Day($1)<= 4, 1, If(Day($1)>= 5 and Day($1)<= 9, 5,
If(Day($1)>=10 and Day($1)<=14,10, If(Day($1)>=15 and Day($1)<=19,15,
If(Day($1)>=20 and Day($1)<=24,20, If(Day($1)>=25 and Day($1)<=31,25)))))) as Group

I would like to create these groups -- but also be able to search for past groups in order to do comparative analysis. That is, the 1st should only be compared to other 1st groupings. And I should be able to link the group to its relevant date (e.g., Jan 1-4, Dec 15-19).

[Anil_Babu_Samineni]

@themanwithnoname Perhaps this, class - script and chart function | Qlik Sense on Windows Help

[themanwithnoname]

Thanks for your reply, Anil.

I don't know whether class would group these properly, since the number of days in each group are not always the same. The first through the fourth is only four days, while the 25th through the end of the month could be four to seven days.

Mostly, though, I am wondering how to compare groups. That is, how can I compare January 1-4 to December 1-4, to November 1-4, etc.

[Chanty4u]

May be try with interval match function.

[pravinboniface]

Your first group has 4 days, the subsequent groups have 5 days and the last group has 7 days.

Instead, if you were using groups of 5 days, such as 1-5, 6-10, 11-15, 16-20, 21-25, 26-30, 31-31, then you can try this mathematical function.

div(day(mm_dd_yyyy)-1,5)*5 + 1

Be sure to document what it does so that the next programmer is not scratching his head 😉

[vinieme12 · Accepted solution]

you have many Options

1) Use IntervalMatch()

2) Create a Mapping table as below then just Map the Groups to your Main Table

MapDayGroups:
Mapping Load
recno() as DayNum
,if(floor(recno(),5)>25,25,if(floor(recno(),5)=0,1,floor(recno(),5))) as Group
Autogenerate (31);

3) Create the Group field in your MasterCalendar as it is based on Day Number

in your calendar script

if(floor(Day($1),5)>25,25,if(floor(Day($1),5)=0,1,floor(Day($1),5))) as Group

[themanwithnoname]

Given the helpful suggestions so far about grouping, how do I then go about comparing them against each other

[themanwithnoname · Accepted solution]

After some thought, I've come to these solutions. (as below, so above)

While I am thankful to everyone who has contributed, I ended up going with Vineeth's third option and modified it for the second part.

//  develop group
If(Floor(Day($1),5)>25,25,         // takes 26-31, floors them to 25
   If(Floor(Day($1),5)=0,1,        // takes a floored 0 back to 1
      Floor(Day($1),5))) as Group, // floors 5, 10, 15, 20

//  determine previous group period relative to today() by flooring twice
If(Floor(Floor(Day(Today()),5)-1,5)>=25,20,
   If(Floor(Floor(Day(Today()),5)-1,5)=0,1,  
   If(Floor(Floor(Day(Today()),5)-1,5)<0,25,
      Floor(Floor(Day(Today()),5)-1,5)))) as PreviousGroup,

//  combine with "MonthsAgo" (as seen/used in autoCalendar)
12*Year(Today())+Month(Today())-12*Year($1)-Month($1) as [MonthsAgo]

These combined, in context, give me the ability to say: where the group=previousgroup and monthsago=n, etc.

Success!