Date filter in pivot table using set expressions

[lalita_sharma]

I have data which looks something like this

Date	Application Name	Operation Status	Target	Onboarding Date	Controls	End Date
01/07/2021	A1	Active	ABC	30/12/2022	Cap A	12/12/2021
01/07/2021	A1	Active	ABC	30/12/2022	Cap B	31/12/2020
01/07/2021	A1	Active	ABC	30/12/2022	Cap C	25/10/2020
01/07/2021	A1	Active	ABC	30/12/2022	Cap D	13/06/2022
01/07/2021	A2	Active	CCC	10/01/2025	Cap A	28/12/2022
01/07/2021	A2	Active	CCC	10/01/2025	Cap B	28/12/2022
01/07/2021	A2	Active	CCC	10/01/2025	Cap C	15/12/2020
01/07/2021	A2	Active	CCC	10/01/2025	Cap D	31/12/2021
01/06/2021	A1	Active	ABC	29/08/2022	Cap A	30/09/2020
01/06/2021	A1	Active	ABC	29/08/2022	Cap B	31/12/2020
01/06/2021	A1	Active	ABC	29/08/2022	Cap C	15/06/2020
01/06/2021	A1	Active	ABC	29/08/2022	Cap D	31/12/2021
01/06/2021	A2	Active	CCC	31/12/2024	Cap A	05/11/2022
01/06/2021	A2	Active	CCC	31/12/2024	Cap B	29/01/2022
01/06/2021	A2	Active	CCC	31/12/2024	Cap C	30/09/2020
01/06/2021	A2	Active	CCC	31/12/2024	Cap D	31/12/2021

The output that I desire is a pivot table where Column Controls becomes column labels and End Date as values in the columns. Also I only want to display records where Date = Max(Date). This filter should be applied using set expression and not a by creating a  filter on filter pane.

Application Name	Operation Status	Target	Onboarding Date	Cap A	Cap B	Cap C	Cap D
A1	Active	ABC	30/12/2022	12/12/2021	31/12/2020	25/10/2020	13/06/2022
A2	Active	CCC	10/01/2025	28/12/2022	28/12/2022	15/12/2020	31/12/2021

I am using pivot table to display the above table and trying to filter on Date using set expression in table measure as below, but the problem is I don’t know how to create measure for my pivot table so that the data is filtered on Max Date without using a aggregation function.

aggregation function({$<Date = Max(Date)>}End Date)

Thanks

Lalita

[JordyWegman · Accepted solution]

Hi Lalita,

If you don't want the user to select anything and they should always see the latest, you have to do this:

Add the dimensions:

• Application Name
• Operation Status
• Target
• =Date( Aggr( Max( {$< Date = {"=Max( {1} Date )"}>} [Onboarding Date] ), [Application Name], [Operation Status], Target ) ) as Onboarding Date
• Controls

Add the measure:

• Date( Max( {$< Date = {"=Max( {1} Date)"}>}[End Date]) )

What we're doing here is calculating the maximum date for both End Date and Onboarding Date. This way the max values for the latest field Date are taken. {1} overrules the selections and therefore selecting anything doesn't work anymore.

I've created this in QlikView, but in Sense it's completely the same:

Jordy

Climber

[lalita_sharma]

Thank you so much @JordyWegman ,  this works perfect for me