Re: Calendar Variable - Qlik Community

rustyfishbones · ‎2017-07-14

Hi All,

I have the following subroutine that creates a calendar, I want to create a calendar based on the Max Date from the data rather than using today(), as sometimes the data I have is a snapshot from a few months ago, so my calendar would be off for the current month etc..

Below is my script, I want to create a variable to capture the max date, and then use that variable in my main LOAD statement

Basically I want to create a variable called vMaxDate and use it instead of using Today()

IF(Month(AddMonths([$(_field)],0)) = Month(Today()), 1, 0) AS [$(_prefix)IsCurrentMonth],

IF(Month(AddMonths([$(_field)],0)) = Month($(vMaxDate) 1, 0) AS [$(_prefix)IsCurrentMonth],

At what point do I add the Variable, I tried the following in various places, but the variable is always blank

LET vMaxDate = MAX([$(_field)]);

SUB CalendarFromField(_field, _calendar, _prefix)

[$(_calendar)]:

// Generate Final Calendar

LOAD

[$(_field)],

YEAR([$(_field)]) AS [$(_prefix)Year],

MONTH([$(_field)]) AS [$(_prefix)Month],

DAY([$(_field)]) AS [$(_prefix)Day],

WEEKDAY([$(_field)]) AS [$(_prefix)Weekday],

WEEK([$(_field)]) AS [$(_prefix)Week],

'Q' & CEIL(MONTH([$(_field)]) / 3 ) AS [$(_prefix)Quarter],

DATE(MONTHSTART([$(_field)]), 'MMM-YYYY') AS [$(_prefix)Month-Year],

WEEK([$(_field)]) & '-' & YEAR([$(_field)]) AS [$(_prefix)Week-Year],

'Week ' & Num(Week ([$(_field)]), '00') & ' - ' & Weekday([$(_field)]) AS [$(_prefix)Week-Day],

IF(DayNumberOfYear([$(_field)]) <= DayNumberOfYear(Today()), 1, 0 ) AS [$(_prefix)IsInYTD],

YEARTODATE([$(_field)])*-1 AS [$(_prefix)CurYTDFlag],

YEARTODATE([$(_field)],-1)*-1 AS [$(_prefix)LastYTDFlag],

IF(DayNumberOfQuarter([$(_field)]) <= DayNumberOfQuarter(Today()), 1, 0) AS [$(_prefix)IsInQTD],

IF(Day([$(_field)]) <= Day(Today()), 1, 0) AS [$(_prefix)IsInMTD],

InWeekToDate([$(_field)], WeekEnd(Today()), 0)*-1 AS [$(_prefix)IsCurrentWeek],

InWeekToDate([$(_field)], WeekEnd(Today()), -1)*-1 AS [$(_prefix)IsLastWeek],

IF(Match(WeekDay([$(_field)]), 'Sat','Sun') = 0, 0, 1) AS [$(_prefix)IsWeekendFlag],

IF(NOT Match(WeekDay([$(_field)]), 'Sat','Sun') = 0, 0, 1) AS [$(_prefix)IsWeekdayFlag],

IF(Month(AddMonths([$(_field)],0)) = Month(Today()), 1, 0) AS [$(_prefix)IsCurrentMonth],

IF(Month(AddMonths([$(_field)],1)) = Month(Today()), 1, 0) AS [$(_prefix)IsLastMonth]

;

// Generate range of dates between min and max.

LOAD

date(DateMin + IterNo()) as [$(_field)] // Link Field

WHILE DateMin + IterNo() <= DateMax

;

// Find min and max of date field values.

LOAD

min(datefield)-1 as DateMin

,max(datefield) as DateMax

;

// Load date field values.

LOAD

FieldValue('$(_field)', RecNo()) as datefield

AutoGenerate FieldValueCount('$(_field)');

END SUB

MK_QSL · ‎2017-07-14

Have you tried this?

The Fastest Dynamic Calendar Script (Ever)

This one will create Calendar with you Min and Max Dates..

rustyfishbones · ‎2017-07-14

Thanks Manish,

But I have been using this calendar for a long time and I want to keep it, but add the additional functionality of basing it on the max date.

Should i be creating the variable outside of the Sub Routine?

Regards

Alan

MK_QSL · ‎2017-07-14

Please clarify

1) Do you want to have MaxDate as per your date field or MaxDate must be Today?

2) What is the issue you are facing in your current calendar subroutine?

Regards,

MK

MK_QSL · ‎2017-07-14

Try this

Sales:
Load * Inline
[
InvoiceDate, Sales
01/01/2016, 100
10/07/2017, 1000
];




SUB CalendarFromField(_field, _calendar, _prefix)
[$(_calendar)]:
//Generate Final Calendar
LOAD
[$(_field)],
YEAR([$(_field)]) AS [$(_prefix)Year],
MONTH([$(_field)]) AS [$(_prefix)Month],
DAY([$(_field)]) AS [$(_prefix)Day],
WEEKDAY([$(_field)]) AS [$(_prefix)Weekday],
WEEK([$(_field)]) AS [$(_prefix)Week],
'Q' & CEIL(MONTH([$(_field)]) / 3 ) AS [$(_prefix)Quarter],
DATE(MONTHSTART([$(_field)]), 'MMM-YYYY') AS [$(_prefix)Month-Year],   
WEEK([$(_field)]) & '-' & YEAR([$(_field)])     AS [$(_prefix)Week-Year],
'Week ' & Num(Week ([$(_field)]), '00') & ' - ' & Weekday([$(_field)]) AS [$(_prefix)Week-Day],
IF(DayNumberOfYear([$(_field)]) <= DayNumberOfYear(Today()), 1, 0 )         AS [$(_prefix)IsInYTD],
YEARTODATE([$(_field)])*-1 AS [$(_prefix)CurYTDFlag],
YEARTODATE([$(_field)],-1)*-1 AS [$(_prefix)LastYTDFlag],
IF(DayNumberOfQuarter([$(_field)]) <= DayNumberOfQuarter(Today()), 1, 0)    AS [$(_prefix)IsInQTD],
IF(Day([$(_field)]) <= Day(Today()), 1, 0)    AS [$(_prefix)IsInMTD],
InWeekToDate([$(_field)], WeekEnd(Today()), 0)*-1        AS [$(_prefix)IsCurrentWeek],
InWeekToDate([$(_field)], WeekEnd(Today()), -1)*-1 AS [$(_prefix)IsLastWeek],
IF(Match(WeekDay([$(_field)]), 'Sat','Sun') = 0, 0, 1) AS [$(_prefix)IsWeekendFlag],
IF(NOT Match(WeekDay([$(_field)]), 'Sat','Sun') = 0, 0, 1) AS [$(_prefix)IsWeekdayFlag],
IF(Month(AddMonths([$(_field)],0))  = Month(Today()), 1, 0) AS [$(_prefix)IsCurrentMonth],
IF(Month(AddMonths([$(_field)],1))  = Month(Today()), 1, 0) AS [$(_prefix)IsLastMonth] 
;
  
// Generate range of dates between min and max.
Load 
Date(_DateStart+(Iterno()-1),'$(DateFormat)' ) as [$(_field)]  
While (_DateStart+(Iterno()-1)<=_DateStop);  

// Find Min and MaxDate
LOAD  
Floor(Min(Fieldvalue('$(_field)',RecNo()))) as _DateStart,  
Floor(Max(Fieldvalue('$(_field)',RecNo()))) as _DateStop  
AUTOGENERATE FieldValueCount('$(_field)');  


END SUB


Call CalendarFromField ('InvoiceDate');

rustyfishbones · ‎2017-07-14

Hi Manish,

I have used the following to get the Max Date within the Sub Routine and it's working

LET vToday = PEEK('Date',-1,'Audit');

Thanks

Regards

Alan

MK_QSL · ‎2017-07-14

This will work only when your Table having date is sorted Ascending Order.