Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Partner
Partner

Last 6 months from end of last full month

I need a flag in my master calendar that flags all dates in the last 6 months from the last full month.

Example today is March 8, 2017. So the last full month is February 2017. I would want this flag to flag all dates in the months

September 2016-February 2017

If the todays date is April 1st I would want the flag to be from

October 2016-March 2017.

Here is my calendar script:

MinMax:
LOAD
Min([ReportingDate]) as MinDate,
//max([ReportingDate]) as MaxDate // use the reporting date as a reference for the month to display as prior
today() as MaxDate //use today's date as a reference for the month to display as prior
Resident
Fact;


Let vMinDate = Num(Peek('MinDate',0,'MinMax'));
Let vMaxDate = Num(Peek('MaxDate',0,'MinMax'));
Let vToday = num(Today());

drop Table MinMax;



//Autogenerate a source table for your calendar
TempCalendar:
Load
$(vMinDate) + RowNo() - 1 as Num,
Date($(vMinDate) + RowNo() - 1) as TempDate
Autogenerate
$(vMaxDate) - $(vMinDate) + 1;


//Generate the Master Calendar
MasterCalendar:
Load
Autonumber('|'&Date(TempDate,'YYYYMM')) as DateNum,
Date(Floor(TempDate)) as [ReportingDate],
Date(WeekStart(TempDate),'M/D/YYYY') as WeekStart,
Date(WeekStart(TempDate), 'M/D/YY') as WeekStart2,
Week(TempDate) as Week,
Year(TempDate) as Year,
chr(39) & right(Year(TempDate),2) as [Short Year],
Month(TempDate) as Month,
AutoNumber(Date(monthstart(TempDate),'MMM-YY')) as MonthID,
MonthStart(TempDate) as MonthStart,
Year(Floor(TempDate)) &
If(Len(Num(Month(Floor(TempDate))))=1, 0 &
Num(Month(Floor(TempDate))),
Num(Month(Floor(TempDate)))) as Period,
Num(Month(TempDate), '00') as MonthNum,
Day(TempDate) as Day,
WeekDay(TempDate) as WeekDay,
'Q' & ceil(Month(TempDate)/3) as Quarter,
'Q' & ceil(Month(TempDate)/3)&'-'&
Right(Year(TempDate),2) as QuarterYear,
Date(monthstart(TempDate),'MMM-YY') as MonthYear,
Week(TempDate) & '-' & Right(Year(TempDate), 2) as WeekYear,
Right(Year(TempDate), 2) & '-' & Week(TempDate) as YearWeek,
inyeartodate(TempDate,$(vToday),0) * -1 as CurYTDFlag,
inyeartodate(TempDate,$(vToday),-1) * -1 as LastYTDFlag,
if(date(TempDate) >= Date(AddMonths(Today(0),-12)), '1') as Rolling12Mo,
if(date(TempDate) >= Date(AddMonths(Today(0),-6)), '1') as Rolling6Mo,
if(date(TempDate) >= Date(Today(0)-7), '1') as Rolling7Day,
if(date(TempDate) = Date(Today(0)-2), '1') as PreviousDay,
if(date(TempDate) >= Date(Today(0)-90), '1') as RollingAvg,
DayNumberOfYear(TempDate) as DayNumberOfYear,
WeekStart( TempDate, 0, 5 ) as WeekStartSaturday,
WeekEnd( TempDate, 0, 5 ) as WeekEndFriday,
WeekStart( TempDate, 0, 5 ) & ' - ' &
WeekEnd( TempDate, 0, 5 ) as WeekInterval

Resident TempCalendar
Order By TempDate ASC;

//Delete temp table
Drop Table TempCalendar;

Thanks in advance

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

Re: Last 6 months from end of last full month

This was the line of code i was looking for to create this flag.

if(date(TempDate) >= Date(AddMonths(MonthStart(Today(0)),-6)) and date(TempDate)< Date(MonthStart(Today(0))), '1') as Rolling6Mo

View solution in original post

1 Reply
Highlighted
Partner
Partner

Re: Last 6 months from end of last full month

This was the line of code i was looking for to create this flag.

if(date(TempDate) >= Date(AddMonths(MonthStart(Today(0)),-6)) and date(TempDate)< Date(MonthStart(Today(0))), '1') as Rolling6Mo

View solution in original post