Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

MAX Date based on other information

Dear all,

I'm tring to identify and set a variable based of the MAX Date included in a fact table for my actuals....this without success.

Fact table:

KEYDATEBudgetActuals
AAA01/01/201313€13€
BBB01/02/201313€13€
CCC01/03/201313€
DDD01/04/201313€
EEE01/05/201313€

I'm actually looking for a MAX formula returning (in my exemple) 01/02/2013.


Thanks

1 Solution

Accepted Solutions
whiteline
Honored Contributor II

Re: MAX Date based on other information

Sorry, my solution is for front-end, not for script.

In Script just use 'where' statement and separate load for TDDate.

Temp:                                                                                 
Load                                                                                  
              
min(Date) as minDate,                                             
              
max(Date) as maxDate

Resident [FACT];      


temp2:

Load                                                                                  
              
max
(Date) as TDDate                                     
Resident
[FACT]

where ACTps<>0;      

4 Replies
whiteline
Honored Contributor II

Re: MAX Date based on other information

Hi.

In this case you could use set analysis to calculate MAX only for the rows filled with actual value.

Something like this: =Max({<Actuals={'*'}>} DATE)

Not applicable

Re: MAX Date based on other information

It is not working for me. I modified it as (max({<ACTps = {0}>} Date) as TDDate ) where ACTps is the name of the ACTUAL Field.


I'm attachign the full script I'm tring to set-up.

------------START_________________

MAPPING LOAD                                                                          

rowno() as Month,                                                                     

'Q' &
Ceil (rowno()/3) as Quarter                                                     

AUTOGENERATE (12);                                                                    


Temp:                                                                                 

Load                                                                                  

              
min(Date) as minDate,                                             

              
max(Date) as maxDate,  

              
max({<ACTps = {0}>} Date) as TDDate                                     

Resident
[FACT];                                                                      

                                                                                      

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));                                     

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

Let varTD = Num(Peek('TDDate', 0, 'Temp'));                                  

DROP Table Temp;                                                                      

                                                                                      

TempCalendar:                                                                         

LOAD                                                                                  

              
$(varMinDate) + Iterno()-1 As Num,                                     

              
Date($(varMinDate) + IterNo() - 1) as TempDate                         

              
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);     

                                                                                      

MasterCalendar:                                                                       

Load                                                                                  

              
TempDate AS Date,                                                                                           

              
Year(TempDate) As Year,                                                

              
Month(TempDate) As Month,                                                                                              

              
InMonth (TempDate, $(varTD),0)*-1 as CMTDFlag,

              
InMonth (TempDate, $(varTD),-1)*-1 as LMTDFlag,

              
InQuarter (TempDate, $(varTD),0)*-1 as CQTDFlag,

              
InQuarter (TempDate, $(varTD),-1)*-1 as LQTDFlag,

              
InYearToDate (TempDate, $(varTD),0)*-1 as CYTDFlag,

              
InYearToDate (TempDate, $(varTD),-1)*-1 as LYTDFlag,                             

              
inyear(TempDate, Monthstart($(varTD)),-1) as RC12,                

              
date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,                   

              
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter                                                       

Resident TempCalendar                                                                 

Order By TempDate ASC;                                                                

Drop Table TempCalendar;                                                              

whiteline
Honored Contributor II

Re: MAX Date based on other information

Sorry, my solution is for front-end, not for script.

In Script just use 'where' statement and separate load for TDDate.

Temp:                                                                                 
Load                                                                                  
              
min(Date) as minDate,                                             
              
max(Date) as maxDate

Resident [FACT];      


temp2:

Load                                                                                  
              
max
(Date) as TDDate                                     
Resident
[FACT]

where ACTps<>0;      

Not applicable

Re: MAX Date based on other information

Thanks! Now I knwo the "where" function.

Community Browser