MAX Date based on other information

[#1]

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:

KEY	DATE	Budget	Actuals
AAA	01/01/2013	13€	13€
BBB	01/02/2013	13€	13€
CCC	01/03/2013	13€
DDD	01/04/2013	13€
EEE	01/05/2013	13€

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

Thanks

[whiteline]

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)

[#3]

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 · Accepted solution]

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;      

[#5]

Thanks! Now I knwo the "where" function.