min/max variable assignment in master calendar

[anonymous1]

hey

i have created a loop to create a master calendar, with each of the date fields in a table. 

Calendar Fields is the name of the table for each data field.  Each date field ends with the word 'Date' so i removed this from the table to create a variable for the date field names, i.e. the fields are called Received Date, Completion Date, Expiry Date:

CalendarFields:
LOAD * INLINE [
Date Field
Received
Completion
Expiry
];

//loop through all dates that require a calendar

FOR x = 0 TO NoOfRows('CalendarFields')-1

//Assign the current date field name to the variable
LET vDateName = PEEK('Date Field', $(x), 'CalendarFields');

LET varMinDate = min([$(vDateName) Date]);
LET varMaxDate = max([$(vDateName) Date]);

i need the max and min values of the calendar to be the max and min values of that particular column, but the above min and max variable declarations are returning null.  does anyone have any advice as to how to get the max and min of each column by using the loop?

TIA

[Anil_Babu_Samineni]

May be just this?

LET varMinDate = min($(vDateName));
LET varMaxDate = max($(vDateName));

[anonymous1]

vDateName is literally taking the names from that table, but i need it to have the word 'Date' appended to the vDateName variable, as the field names all end in the word 'Date', e.g. 'Expiry Date'

i also tried this:

LET varMinDate = min([$(vDateName) Date]);
LET varMaxDate = max([$(vDateName) Date]);

but this still pulls through NULL. 😞

[Anil_Babu_Samineni]

Can you get little more, How you need for Min and Max. What is the expected?

[anonymous1]

hi, 

im creating a master calendar so i need the max and min dates for each of the fields i wish to create a calendar on.  i need the min/max of each of the fields in the table with the name appended with the word 'Date'.  

[anonymous1]

if the variable vDateName is returning "Expiry", i don't understand why the following doesnt return the min and max Expiry Date.

LET varMinDate = min([$(vDateName) Date]);
LET varMaxDate = max([$(vDateName) Date]);

[Anil_Babu_Samineni]

This is not even Parameter analysis hence this won't work. I am sorry, I still didn't understand your intention?

[Brett_Bleess]

Natasha, I suspect you may need more than one calendar in this use case, see the following Design Blog post:

https://community.qlik.com/t5/Qlik-Design-Blog/Canonical-Date/ba-p/1463578

Regards,
Brett

[marcus_sommer]

Your approach couldn't work because with functions like peek() or fieldvalue() you could pick only a single value from a table respectively a field. If you need any kind of an evaluation of data you need a load-statement to create them and afterwards you may pick those values again, for example:

MinMaxTemp:
load num(max(fieldvalue('Date1', recno()))) as MaxTemp
          num(min(fieldvalue('Date1, recno()))) as MinTemp
autogenerate fieldvaluecount('Date1');

load num(max(fieldvalue('Date2', recno()))) as MaxTemp
          num(min(fieldvalue('Date2, recno()))) as MinTemp
autogenerate fieldvaluecount('Date2');

load num(max(fieldvalue('Date3', recno()))) as MaxTemp
          num(min(fieldvalue('Date3, recno()))) as MinTemp
autogenerate fieldvaluecount('Date3');

MinMax:
load num(max(fieldvalue('MaxTemp', recno()))) as Max
          num(min(fieldvalue('MinTemp, recno()))) as Min
autogenerate fieldvaluecount('Date3');

LET varMinDate = PEEK('Min', 0, 'MinMax');
LET varMaxDate = PEEK('Max', 0, 'MinMax');

drop tables MinMax, MinMaxTemp;

This example assumed that you don't need 3 different periods within the calendar(s). Furthermore I doubt that you need the above at all because quite usually you know already your earliest date-value and could set it fix and the latest date is often something like today() or maybe yearend(today()).

- Marcus