How to get the maximum date / last date from the field? Is there any function for it?
I am not looking for the monthend date, the date would be random in the field could be the mid of month date so monthend function wouldn't works here... [:s]
the error in ur expression is u are using aggregate fn within a aggregate fn and this never works. what you can do is calculate max date first and the use count .
e.g if( day( loadDate_active = day(max(loaddate),count(AIF))
this shud work
none of ur code works...
Below is the only option which i can use... ... any ways i was looking for maximum date option not yesterday date but i can survive with it...
=count(if((day(LoadDate_Active)=(day(today())-1)) and (AIF='P'), AIF))
I Have a calucaltion based on max date and use the following function
aggr(max([Date]),Dimension1, Dimension2, Dimension3) as a calculated dimension to show price on this date.
I also order the load by date in the script which I think may be key to solving your issue
Can you do this in the script?
LOAD max(StockValuationDate_Key) as LatestStockValuationDate_Key
FROM factsvfifo.qvd (qvd);
Let svdateint=Peek('LatestStockValuationDate_Key',-1,'lastfactsvfifo'); // Takes the last read record from factsvfifo
Let svdate = Date(Mid('$(svdateint)',7,2) & '/' & Mid('$(svdateint)',5,2) & '/' & Left('$(svdateint)',4), 'DD/MM/YYYY');
Drop Table lastfactsvfifo;
Then you just use your variable.