topic Re: MAX Date based on other information in QlikView

MAX Date based on other information

Sun, 25 Aug 2013 17:01:17 GMT

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

Re: MAX Date based on other information

whiteline — Sun, 25 Aug 2013 17:38:46 GMT

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

Sun, 25 Aug 2013 18:35:36 GMT

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

whiteline — Sun, 25 Aug 2013 19:30:04 GMT

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

Sun, 25 Aug 2013 21:37:43 GMT

Thanks! Now I knwo the "where" function.