Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jblomqvist
Specialist
Specialist

Anyone know how to fix my MinMax table in this Master Calender script? It uses FieldValue()

Hello friends,

In the script below I am trying to get my master calendar to work using a MinMax table that uses FieldValue function rather than a resident load of dates.

But I cannot get the calender to work using this. Anyone got an idea of how to fix this?

/////---------------------------Script-------------------------------------------

Orders:

LOAD * INLINE [

    OrderID, OrderDate

    1, 20/11/2011

    2, 20/11/2011

    3, 01/02/2012

    4, 31/01/2012

    5, 01/06/2014

    6, 15/07/2014

    7, 01/01/2016

];

////----Master Calender--------

//MinMax1:

//LOAD

//    Min(OrderDate) AS MinDate1,

//    Max(OrderDate) AS MaxDate1

//RESIDENT Orders;

MinMax:

LOAD

Min(FieldValue('OrderDate',RecNo()))-1 as MinDate,

Max(FieldValue('OrderDate',RecNo())) as MaxDate

AutoGenerate(FieldValueCount('OrderDate'));

// ***** Set up the variables *****   

LET vMinDate = NUM(PEEK('MinDate', 0, 'MinMax'));

LET vMinDate2 = NUM(PEEK('MinDate', 0, 'MinMax'));

LET vMaxDate = NUM(PEEK('MaxDate', 0, 'MinMax'));

LET vToday = $(vMaxDate);

// ***** Temporary Calendar *****

TempCal:

LOAD

    DATE($(vMinDate) + ROWNO()-1) AS TempDate

AUTOGENERATE

    $(vMaxDate) - $(vMinDate) +1;

//DROP TABLE MinMax;

// ***** The Master Calendar *****

MasterCalendar:

LOAD

    TempDate AS OrderDate,

    WEEK(TempDate) AS Week,

    YEAR(TempDate) AS Year,

    MONTH(TempDate) AS Month,

    DAY(TempDate) AS Day,

    WEEKDAY (TempDate) AS Weekday,

    'Q' & CEIL(MONTH(TempDate) / 3) AS Quarter,

    CEIL(MONTH(TempDate)) AS Quarter1,

    DATE(MONTHSTART(TempDate), 'MMM-YYYY') AS MonthYear,

    WEEK(TempDate) & '-' & YEAR(TempDate) AS WeekYear,

    INYEARTODATE(TempDate, $(vToday), 0) * -1 AS CurYTDFlag,

    INYEARTODATE(TempDate, $(vToday), -1) * -1 AS LastYTDFlag,

    MonthStart(TempDate) as MonthStart

RESIDENT TempCal

ORDER BY TempDate ASC;

DROP TABLE TempCal;

11 Replies
jblomqvist
Specialist
Specialist
Author

Hi guys,

Thanks for your input.

I guess the last thing is I don't understand why it creates a "?" output? Never seen that before.

Is it a bug?

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

The "?" is an oddity when using an aggregation function -- like min() -- with FieldValue input. The internal value is correct, but it seems to be confused as to what to use for a display value. It doesn't cause any problem, especially since in normal circumstances you drop the fields.

-Rob