Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Master II
Master II

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;      

View solution in original post

4 Replies
whiteline
Master II
Master II

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
Author

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
Master II
Master II

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
Author

Thanks! Now I knwo the "where" function.