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

Tue, 22 Sep 2015 20:39:34 GMT

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.

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

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

jolivares — Tue, 22 Sep 2015 21:33:14 GMT

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

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

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

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

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