Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Using Max (User Selected Date) to display Pivot table dimension

Hello,

I'm running into an issue where I need to take the value from one table " UltimateBroker" that coincides with whatever the max date the user selects from the calendar.

___qlik.PNG

So if a user picks a date range from 1/1/2015 to 3/31/2015, I need to take the value in UltimateBrokerName that matches the max date which in this case would be 3/31/2015. The UltimateBrokerName is being used in a pivot table as a dimension. Another criteria is if the user picks a date range where the specific broker has 2 ultimate brokers. From 1/1/2015 to 2/15/2015 it shows a specific broker to have Ultimate Broker A, from 2/16/2015 - 12/31/3999 that same broker now rolls up to Ultimate Broker B. In this situation ALL data from 1/1/2015 - 3/31/2015 will roll up to Ultimate Broker B since the max date = Ultimate Broker B row in the table.

How/Where do I put logic that can change what UltimateBrokerName(dimension) each broker (another dimension) rolls up to and then show MoneyAmount(expression) based on what date range the user picks.

Pivot Table currently looks like this, we are open to change design if the UltimateBrokerName needs to become an expressions.

UltimateBrokerName

               Broker Name1          $$$$

               Broker Name2          $$$$

UltimateBrokerName 2

               Broker Name 3          $$$$

               Broker Name 4          $$$$

1 Reply
Highlighted
Specialist
Specialist

Re: Using Max (User Selected Date) to display Pivot table dimension

Asuming you need to get the name of the broker for max date, try with this expression:

FirstSortedValue(Broker,-Aggr(Max(Date),Date))