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: 
anonymous1
Contributor III
Contributor III

min/max variable assignment in master calendar

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

Labels (3)
8 Replies
Anil_Babu_Samineni

May be just this?

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
anonymous1
Contributor III
Contributor III
Author

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?

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
anonymous1
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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?

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Brett_Bleess
Former Employee
Former Employee

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
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