MasterCalendar: Configure all the period with the last day for each date

[HS_1]

Hello everyone,

I'm looking for help with configuring my MasterCalendar in Qlik Cloud.
I want to display the number of active users for each period (week, month, quarter, year), based on the last available date of that period.

For example:

• For May, I want to reflect the status as of May 31st.

• For April, it should be the status on April 30th, and so on.

The goal is to show the number of active users at the end of each period, so the calendar needs to consider only the last day of each time unit.

Here is my current MasterCalendar:

SUB MasterCalendarIntune(Date_Key)
[MasterCalendar_Intune]:

LOAD
// --- Year ---
Year(Date_Intune) as Year_Intune,
YearStart(Date_Intune) as YearStart_Intune,

if(Mod(Date_Intune,400)=0,1,if(Mod(Date_Intune,100)=0,0,if(Mod(Date_Intune,4)=0,1,0))) as IsLeapYear_Intune,

// --- Quarter ---
Dual('Q' & Ceil(Month(Date_Intune)/3), Ceil(Month(Date_Intune)/3)) as Quarter_Intune,
QuarterStart(Date_Intune) as QuarterStart_Intune,
Dual(Year(Date_Intune) & ' Q' & Ceil(Month(Date_Intune)/3), QuarterStart(Date_Intune)) as YearQuarter_Intune,

// --- Month ---
Month(Date_Intune) as Month_Intune,
MonthStart(Date_Intune) as MonthStart_Intune,
Date(MonthStart(Date_Intune), 'YYYY MMM') as YearMonth_Intune,
Mod(Month(Date_Intune) - 1, 3) + 1 as MonthOfQuarter_Intune,

// --- Week ---
WeekYear(Date_Intune) & '-' & Week(weekstart(Date_Intune)) as WeekYear_Intune,
WeekDay(Date_Intune) as WeekDay_Intune,
Week(Date_Intune) as Week_Intune,

// --- Day ---
Date_Intune as [$(Date_Key)],
Date_Intune - YearStart(Date_Intune) + 1 as DayOfYear_Intune,
Date_Intune - QuarterStart(Date_Intune) + 1 as DayOfQuarter_Intune,
Day(Date_Intune) as DayOfMonth_Intune,
Date_Intune as Date_Intune
;

LOAD
Year(TempDate_Intune) as Year_Intune,
Ceil(Month(TempDate_Intune)/3) as Quarter_Intune,
Month(TempDate_Intune) as Month_Intune,
TempDate_Intune as Date_Intune
;

LOAD
Date(mindate_Intune + IterNo()) as TempDate_Intune,
maxdate_Intune
WHILE mindate_Intune + IterNo() <= maxdate_Intune;

LOAD
Min(FieldValue('$(Date_Key)', RecNo())) - 1 as mindate_Intune,
Max(FieldValue('$(Date_Key)', RecNo())) as maxdate_Intune
AUTOGENERATE FieldValueCount('$(Date_Key)');

END SUB

[henrikalmen · Accepted solution]

You could create a flag like this:

if(floor(Date_Intune)=floor(MonthEnd(Date_Intune)), 1, 0) as isLastDayOfMonth

You can use that flag to filter out only the last day of each month.

[Bhushan_Mahajan]

@HS_1 You can create date from Master Calendar and in the front end your date should be equal to respective months end date using MonthEnd function 

[HS_1]

Thank you, It work with this method

[HS_1]

Thank you for your answer, the method of @henrikalmen work also