Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
Could you be a bit more specific about the issue? What exactly is not working?
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.
maybe
MinMax:
LOAD
Date(Min(FieldValue('OrderDate',RecNo()))-1) as MinDate,
Date(Max(FieldValue('OrderDate',RecNo()))) as MaxDate
AutoGenerate(FieldValueCount('OrderDate'));
The code you posted is working fine I guess. What is not working for you?
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
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()
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
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
I had a ? Date or Num solved it.
I've seen the ? value in a field some other (few) time.