topic Picking The Max Date For Each Row in QlikView

Picking The Max Date For Each Row

Anonymous — Fri, 27 Nov 2015 17:30:53 GMT

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 ?

Re: Picking The Max Date For Each Row

swuehl — Fri, 27 Nov 2015 17:37:01 GMT

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

Re: Picking The Max Date For Each Row

marcus_sommer — Fri, 27 Nov 2015 17:43:20 GMT

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

Re: Picking The Max Date For Each Row

Anonymous — Fri, 27 Nov 2015 20:05:52 GMT

Thank you swuehl‌ but that didn't work.

Re: Picking The Max Date For Each Row

Anonymous — Fri, 27 Nov 2015 20:06:37 GMT

Thank you Marcus_Sommer‌.