Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
rustyfishbones
Honored Contributor II

Calendar Variable

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

Tags (4)
6 Replies
MVP
MVP

Re: Calendar Variable

Have you tried this?

The Fastest Dynamic Calendar Script (Ever)

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

rustyfishbones
Honored Contributor II

Re: Calendar Variable

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

MVP
MVP

Re: Calendar Variable

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

MVP
MVP

Re: Calendar Variable

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
Honored Contributor II

Re: Calendar Variable

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

MVP
MVP

Re: Calendar Variable

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