Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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)
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;
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;
Thanks! Now I knwo the "where" function.