Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ?
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
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))
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
Thank you swuehl but that didn't work.
Thank you Marcus_Sommer.