Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
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‌.