I tried it from the first time and it doesn't work , I don't know why because the =count({<[Calendar date]=, [%Year month SEQ]={$(=max([%Year month SEQ]))}>}Document) work
Do you have other date and time related filters where you might make a selection? Like Month or Year etc? If you do, make sure to ignore them in your set analysis as well
CREATED, Month(CREATED) & Year(CREATED) as Date, num(Month(CREATED)) & num(Year(CREATED)) as numDate, day(CREATED) as Day, day(UPDATED) - day(CREATED) as Day_Taken,
and I have this master calendar :
Let vMinDate=num(MakeDate(2016,12,31)); //Let vMinDate= AddYears(today(),-2); Let vMaxDate=Today()+30; NoConcatenate [D_Calendar_TMP]: Load date($(vMinDate)+ RowNo()) as [Calendar date] AutoGenerate $(vMaxDate)-$(vMinDate); NoConcatenate [D_Calendar_TMP2]: Load RowNo() as [%Date SEQ] ,AutoNumber(text(date([Calendar date], 'DD/MM/YYYY'))) as [%Date ID] ,date([Calendar date], 'DD/MM/YYYY') as [Calendar date] ,Day([Calendar date]) as [Day of date] ,'W' & right(WeekName([Calendar date],0,-4), 2) as [week date] ,'Q' & Num(ceil(month([Calendar date])/3), '00') as [quarter date] , right(WeekName([Calendar date],0,-4), 2) as [Week of date] ,Month([Calendar date]) as [Month of date] ,QuarterName([Calendar date]) as [Quarter of date] ,Year([Calendar date]) as [Year of date] , right(WeekName([Calendar date],0,-4), 2) &'/'&Year([Calendar date]) as [Year week date] ,date(MonthStart([Calendar date]),'MMM YYYY') as [Year month date] ,num( Year([Calendar date]) ) & '- Q' & Num(ceil(month([Calendar date])/3), '00') as [Year quarter date] ,WeekStart([Calendar date],0,-4) as [Week start date] ,WeekEnd([Calendar date],0,-4) as [Week end date] ,MonthStart([Calendar date]) as [Month start date] ,MonthEnd([Calendar date]) as [Month end date] ,QuarterStart([Calendar date]) as [Quarter start date] ,QuarterEnd([Calendar date]) as [Quarter end date] ,YearStart([Calendar date]) as [Year start date] ,YearEnd([Calendar date]) as [Year end date] ,AutoNumber(WeekEnd([Calendar date],0,-4)) as [%Year week SEQ] ,AutoNumber(Num(Month([Calendar date]), '00')&'/'&Year([Calendar date])) as [%Year month SEQ] ,AutoNumber(Num(QuarterName([Calendar date]), '00')&'/'&Year([Calendar date])) as [%Year quarter SEQ] ,if(monthstart(date([Calendar date],'MM/YYYY'))= monthstart(date(Today() ,'MM/YYYY')) ,monthstart(date(AddMonths(Today(),-2),'MM/YYYY')), monthstart(date(AddMonths([Calendar date] ,-1),'MM/YYYY'))) as [Month Fact] Resident D_Calendar_TMP;
join(D_Calendar_TMP2) Load max([%Date ID]) as [%Date ID] ,1 as [Week end flag] Resident D_Calendar_TMP2 Group by [Week end date];
join(D_Calendar_TMP2) Load max([%Date ID]) as [%Date ID] ,1 as [Month end flag] Resident D_Calendar_TMP2 Group by [Month end date];
join(D_Calendar_TMP2) Load max([%Date ID]) as [%Date ID] ,1 as [Quarter end flag] Resident D_Calendar_TMP2 Group by [Quarter end date];
join(D_Calendar_TMP2) Load max([%Date ID]) as [%Date ID] ,1 as [Year end flag] Resident D_Calendar_TMP2 Group by [Year end date];
join(D_Calendar_TMP2) Load min([%Date ID]) as [%Date ID] ,1 as [Week start flag] Resident D_Calendar_TMP2 Group by [Week start date];
join(D_Calendar_TMP2) Load min([%Date ID]) as [%Date ID] ,1 as [Month start flag] Resident D_Calendar_TMP2 Group by [Month start date];
join(D_Calendar_TMP2) Load min([%Date ID]) as [%Date ID] ,1 as [Quarter start flag] Resident D_Calendar_TMP2 Group by [Quarter start date];
join(D_Calendar_TMP2) Load min([%Date ID]) as [%Date ID] ,1 as [Year start flag] Resident D_Calendar_TMP2 Group by [Year start date];
NoConcatenate D_Calendar: Load [%Date SEQ] ,[%Date ID] ,[Calendar date] ,[Day of date] ,[Week of date] ,[Month of date] ,[Quarter of date] ,[Year of date] ,[Year week date] ,[Year month date] ,[week date] ,[quarter date] ,[Year quarter date] ,[Week start date] ,[Week end date] ,[Month start date] ,[Month end date] ,[Quarter start date] ,[Quarter end date] ,[Year start date] ,[Year end date] ,[%Year week SEQ] ,[%Year month SEQ] ,[%Year quarter SEQ] ,[Month Fact] ,Num ( (date([Calendar date],'DD/MM/YYYY') - date([Month start date],'DD/MM/YYYY')) + 1) AS [Month Diff] ,Num ( (date([Calendar date],'DD/MM/YYYY') - date([Quarter start date],'DD/MM/YYYY')) + 1) AS [Quarter Diff] ,Num ( (date([Calendar date],'DD/MM/YYYY') - date([Year start date],'DD/MM/YYYY')) + 1) AS [Year Diff] ,if(WeekEnd([Calendar date])=WeekEnd(Date(Today()-1,'DD/MM/YYYY')),Date(Today()-1,'DD/MM/YYYY'), WeekEnd([Calendar date])) as [Week end date 2] ,if(MonthEnd([Calendar date])=MonthEnd(Date(Today()-1,'DD/MM/YYYY')),date(Date(Today()-1,'DD/MM/YYYY'),'MMM YYYY'), date(MonthEnd([Calendar date]),'MMM YYYY') ) as [Month end date 2] ,if(QuarterEnd([Calendar date])=QuarterEnd(Date(Today()-1,'DD/MM/YYYY')),date(Date(Today()-1,'DD/MM/YYYY'),'MMM YYYY'), date(QuarterEnd([Calendar date]),'MMM YYYY') ) as [Quarter end date 2] ,if(YearEnd([Calendar date])=YearEnd(Date(Today()-1,'DD/MM/YYYY')),date(Date(Today()-1,'DD/MM/YYYY'),'MMM YYYY'), date(YearEnd([Calendar date]),'MMM YYYY') ) as [Year end date 2]
//,if([Calendar date]=Date(Today()-1,'DD/MM/YYYY'),1,[Week end flag]) as [Week end flag]
, if(date(floor( WeekEnd([Calendar date],0,-4)))=date(floor(WeekEnd(Today()-1,0,-4))), if([Calendar date]=Date( '$(vCheckRevenuDate)','DD/MM/YYYY'),1),[Week end flag]) as [Week end flag]
,if(date(floor( MonthEnd([Calendar date])))=date(floor(MonthEnd(Date(Today()-1,'DD/MM/YYYY')))), if([Calendar date]=Date( '$(vCheckRevenuDate)','DD/MM/YYYY'),1),[Month end flag]) as [Month end flag]
,if( date(floor(QuarterEnd([Calendar date])))=date(floor(QuarterEnd(Date(Today()-1,'DD/MM/YYYY')))), if( [Calendar date]=Date('$(vCheckRevenuDate)','DD/MM/YYYY'),1) ,[Quarter end flag] ) as [Quarter end flag]
//,if([Calendar date]=Date(Today()-1,'DD/MM/YYYY'),1,[Year end flag]) as [Year end flag]
, if(date(floor( YearEnd([Calendar date])))=date(floor(YearEnd(Date(Today()-1,'DD/MM/YYYY')))), if([Calendar date]=Date( '$(vCheckRevenuDate)','DD/MM/YYYY'),1),[Year end flag]) as [Year end flag]
