for loop with dates

[#1]

hello,

this is my script:

S_PERSONAL_A_Team:

LOAD

  date('01/'&RIGHT(KEY_4,7),'DD/MM/YYYY') as MONTH_YEAR, // '01/06/2016'

  Team as S_TEAM_NAME,

    MAX_PERSONAL_AVG as S_MAX_PERSONAL_AVG

RESIDENT PERSONAL_A_Team;

TEMP_MAX_MIN_DATE:

LOAD MAX(MONTH_YEAR) AS MAX_S_MONTH_YEAR,

  MIN(MONTH_YEAR) AS MIN_S_MONTH_YEAR

RESIDENT S_PERSONAL_A_Team;

SET DateFormat='DD/MM/YYYY'; 

LET vMinTempDate = Peek('MIN_S_MONTH_YEAR',0,'TEMP_MAX_MIN_DATE'); 

LET vMaxTempDate = Peek('MAX_S_MONTH_YEAR',0,'TEMP_MAX_MIN_DATE'); 

FOR i= $(vMinTempDate) to $(vMaxTempDate)

let Z= '01'&'/'&right(date($(i)),7);  // since the loop running on months i always set it to the first day

let j= date(AddMonths('01'&'/'&right(date($(i)),7),-2),'DD/MM/YYYY'); // variable of two months ago

TEMP_S_PERSONAL_A_Team:

LOAD  S_TEAM_NAME&'/'&right(date($(Z)),7) as KEY_4,

S_TEAM_NAME as S_TEAM_NAME_NEW,

  $(Z) as new_S_MONTH_YEAR,

// $(j) as two_months_ago,

MAX(S_MAX_PERSONAL_AVG) as maxScore

RESIDENT S_PERSONAL_A_Team

where (S_MONTH_YEAR<=$(Z) and S_MONTH_YEAR>=$(j))

Group by S_TEAM_NAME,$(Z);

NEXT i

///////////////

I want to get a table that for each date (new_S_MONTH_YEAR) I will get the maxSCore from i to j

my problem is that when I executing script 0 line fetched from TEMP_S_PERSONAL_A_Team.

[m_woolf]

Try checking the value for vMinTempDate and vMaxTempDate.

These must be numbers, not dates.

[#3]

If you try with FirstSortedValue() ?

https://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/Scripting/AggregationFunctions/f...

[sfatoux72]

‌Hi,

your first LOAD seems to be wrong.

The function Date(...) is wrong because the first parameter of this function need to be a number.

In your case, you need to use Date#(...) where the first paramete is a text.

S_PERSONAL_A_Team:

LOAD

  Date#('01/'&RIGHT(KEY_4,7),'DD/MM/YYYY') as MONTH_YEAR, // '01/06/2016'

  Team as S_TEAM_NAME,

    MAX_PERSONAL_AVG as S_MAX_PERSONAL_AVG

RESIDENT PERSONAL_A_Team;

[#5]

You were right, I added Num() and it worked, thanks