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

[jblomqvist]

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;

[tresesco]

Could you be a bit more specific about the issue? What exactly is  not working?

[jblomqvist]

Hi,

I was trying to implement rwunderlich‌'s suggestion here http://qlikviewcookbook.com/2015/05/better-calendar-scripts/

See Inefficient Code part of the page.

I want to get that bit working in my example but it doesn't.

[maxgro]

maybe

MinMax:

LOAD

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

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

AutoGenerate(FieldValueCount('OrderDate'));

[tresesco]

The code you posted is working fine I guess. What is not working for you?

[rwunderlich]

As treseco said, it worjs fine when I run it. If you are concerned that the MaxDate value displays as a "?", wrap it in a Date() as maxgro suggested.

What problem are you experiencing?

-Rob

[Gabriel]

Hi,

This should fix it for you

MinMax:

LOAD

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

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

AutoGenerate FieldValueCount('OrderDate');

Or you can change the NUM() function to Date()

[jblomqvist]

Hi Gabriel, rwunderlich and guys,

I guess so the above suggestion works. I had never come across a question mark output before so thought didn't understand it.

But if I include -1 then it give me the day before my min date (e.g. 19th from my inline load)

If I remove the -1 then it starts on the actual first date.

So is -1 really necessary?

And what does FieldValueCount('OrderDate') do? I think it create a pool of distinct dates in the table which we later use for Min Max variables. If I put Autogenerate 1; instead, it will not work because it only has the min field?

Sorry if I am asking stupid questions I am trying to understand the whole picture of this new technique

[rwunderlich]

The reason you are one day before the mindate is that you are using -1 twice. Once on the Min(FieldValue... and again on recno() in the TempCal. You only need  it one place or the other.

FieldValueCount('field') returns the number of unique values for 'field', thus running your load loop for the number of values of OrderDate. Take a look in the QV Help to learn about the functions.

-Rob

[maxgro]

I had a ?  Date or Num solved it.

I've seen the ? value in a field some other (few) time.