topic Re: min/max variable assignment in master calendar in QlikView

min/max variable assignment in master calendar

anonymous1 — Sat, 16 Nov 2024 02:52:19 GMT

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

Re: min/max variable assignment in master calendar

Anil_Babu_Samineni — Tue, 30 Jul 2019 12:25:55 GMT

May be just this?

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

Re: min/max variable assignment in master calendar

anonymous1 — Tue, 30 Jul 2019 12:35:26 GMT

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. 😞

Re: min/max variable assignment in master calendar

Anil_Babu_Samineni — Tue, 30 Jul 2019 12:42:49 GMT

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

Re: min/max variable assignment in master calendar

anonymous1 — Tue, 30 Jul 2019 12:46:04 GMT

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'.

Re: min/max variable assignment in master calendar

anonymous1 — Tue, 30 Jul 2019 12:52:12 GMT

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]);

Re: min/max variable assignment in master calendar

Anil_Babu_Samineni — Tue, 30 Jul 2019 12:57:52 GMT

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

Re: min/max variable assignment in master calendar

Brett_Bleess — Wed, 21 Aug 2019 15:14:56 GMT

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

Re: min/max variable assignment in master calendar

marcus_sommer — Thu, 22 Aug 2019 11:55:48 GMT

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