4 Replies Latest reply: Aug 25, 2013 5:37 PM by Francesco Ravagnolo

# 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

• ###### 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)

• ###### 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;

• ###### 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;

• ###### Re: MAX Date based on other information

Thanks! Now I knwo the "where" function.