Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
We value your feedback – take our 5-minute QlikView modernization survey
cancel
Showing results for 
Search instead for 
Did you mean: 
arsalanam
Creator
Creator

Picking The Max Date For Each Row

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 ?

1 Solution

Accepted Solutions
marcus_sommer
MVP & Luminary
MVP & Luminary

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

View solution in original post

4 Replies
swuehl
MVP
MVP

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
MVP & Luminary
MVP & Luminary

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

arsalanam
Creator
Creator
Author

Thank you swuehl‌ but that didn't work.

arsalanam
Creator
Creator
Author

Thank you Marcus_Sommer‌.