Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
tschullo
Creator II
Creator II

Applying Historic Table to Monthly Report Data

I have a difficult conundrum:

I have monthly report of account info with a Report Period  of Jan 2020, Feb 2020 etc.
(representing end of month snapshot)

I have another table that keeps a history of overrides to those accounts, including override type (A, B,C) and date of override.  these overrides can occur from zero to n number of times a month, so not necessarily every month.

I need to be able to show the current override type for a given report period selection.

If no selection is made, the latest override would be shown, if a selection is made, the max override date and value within that selection would be shown in my pivot table.

History table

AccountOverride DateOverride type
1012/2/2020C
1012/23/2020C
1021/1/2020A
1023/2/2020C
10312/12/2019A

 

Account table (includes report date, but date is not in pivot)  Solution must respect Report Date selection

If no selection is made, max values are shown from history

AccountCurrent Override Code
101C
102A
103A

 

If Feb 2020 is selected, values would be:

AccountCurrent Override Code
101C
102C
103A

 

Any ideas?

Labels (2)
1 Reply
SerhanKaraer
Contributor III
Contributor III

Hi Tschullo,

You can use FirstSortedValue function.

FirstSortedValue (distinct ] value, sort-weight [, rank ])

FirstSortedValue("Override type",-"Override Date")

sort-weight is ascending order by default, to turn it into descending just put minus sign before the field.

To show/hide "Override Date", you can write GetSelectedCount(YourReportPeriodField)=0 in "show column if " property in dimension/measure.