Month-1 - Qlik Community

zied_ahmed1 · ‎2018-12-10

Hello I need to calcule the count of the Month -1 Week and Week-1 ,

For the actual Month i use this one :

=count({<[Calendar date]=, [%Year month SEQ]={$(=max([%Year month SEQ]))}>}Document)

the year month Seq is in the script :

AutoNumber(Num(Month([Calendar date]), '00')&'/'&Year([Calendar date])) as [%Year month SEQ]

Thanks for help :

OmarBenSalem · ‎2018-12-10

=count({<[Calendar date]=, [%Year month SEQ]={$(=max([%Year month SEQ])-1)}>}Document)

zied_ahmed1 · ‎2018-12-10

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

sunny_talwar · ‎2018-12-10

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

zied_ahmed1 · ‎2018-12-10

In the table I have this :

Year(CREATED) as Year,
Month(CREATED) as Month,

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]

, [Week start flag]
, [Month start flag]
, [Quarter start flag]
, [Year start 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 start flag] ) as [Month start flag]
,AutoNumber(Num(Month([Month Fact]), '00')&'/'&Year([Month Fact]), '%Year month fact SEQ') as [%Year month fact SEQ]
Resident D_Calendar_TMP2;

Drop table D_Calendar_TMP, D_Calendar_TMP2;