Picking The Max Date For Each Row

[Anonymous]

Trying to implement a logic in Set Analysis where there are two date fields i.e. Trade Date and business Date. One Trade Date can have one or multiple Business Dates ... I have a Straight Chart and one of the expressions is supposed to show Sales for the Max(Business Date) for the given Trade Date, which is in Dimension.

Currently, my expression is:

=Sum(Aggr(Sum({<BusinessDate={"$(=Max(Date(BusinessDate),'YYYY-MM-DD'))"}>} Sales), TradeDate, ItemNo, ItemGroupName))

But apparently this Max date logic in Set Analysis always gives one Date, which is the Max Date for current Selections and not for each row for that Trade Date.

I would assume since TradeDate, ItemNo and ItemGroupName are part of Dimensions, this logic should work.

Any suggestions ?

[swuehl]

Set analysis is evaluated once per chart, won't respect dimension values.

Try maybe

=Sum( Aggr( Sum(If( BusinessDate = Max(TOTAL<TradeDate, ItemNo, ItemGroupName> BusinessDate), Sales)), TradeDate, ItemNo, ItemGroupName, BusinessDate))

[marcus_sommer · Accepted solution]

In your case will be max. BusinessDate one time globally calculated before the chart-calculations one row-level happens and applied for them. Are the sales for one BusinessDate a single-value you could use firstsortedvale() for this, like:

Sum(Aggr(Firstsortedvalue(Sales, -BusinessDate), TradeDate, ItemNo, ItemGroupName))

- Marcus

[Anonymous]

Thank you swuehl‌ but that didn't work.

[Anonymous]

Thank you Marcus_Sommer‌.