Get a maxdate / last date in the field

[shumailh]

Hi,

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]

Regards,
Shumail Hussain

[#2]

Can this be used?

=max(Date)

Or will you explain the query in more detail?

[shumailh]

I have tried this one but it wasn't working..

My expression is :

=Count(if(day(LoadDate_Active) = Day(max(LoadDate)), AIF)

Regards,
Shumail Hussain

[#4]

try this ............

=day(date(max(date column)))

this works fin @ my end

[#5]

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

[shumailh]

Thanks Archana,

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

Regards,
Shumail Hussain

[#7]

Hi

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

Matt

[#8]

Can you do this in the script?

lastfactsvfifo:

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.