Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
lalita_sharma
Contributor III
Contributor III

Date filter in pivot table using set expressions

I have data which looks something like this

DateApplication NameOperation StatusTargetOnboarding DateControlsEnd Date
01/07/2021A1ActiveABC30/12/2022Cap A12/12/2021
01/07/2021A1ActiveABC30/12/2022Cap B31/12/2020
01/07/2021A1ActiveABC30/12/2022Cap C25/10/2020
01/07/2021A1ActiveABC30/12/2022Cap D13/06/2022
01/07/2021A2ActiveCCC10/01/2025Cap A28/12/2022
01/07/2021A2ActiveCCC10/01/2025Cap B28/12/2022
01/07/2021A2ActiveCCC10/01/2025Cap C15/12/2020
01/07/2021A2ActiveCCC10/01/2025Cap D31/12/2021
01/06/2021A1ActiveABC29/08/2022Cap A30/09/2020
01/06/2021A1ActiveABC29/08/2022Cap B31/12/2020
01/06/2021A1ActiveABC29/08/2022Cap C15/06/2020
01/06/2021A1ActiveABC29/08/2022Cap D31/12/2021
01/06/2021A2ActiveCCC31/12/2024Cap A05/11/2022
01/06/2021A2ActiveCCC31/12/2024Cap B29/01/2022
01/06/2021A2ActiveCCC31/12/2024Cap C30/09/2020
01/06/2021A2ActiveCCC31/12/2024Cap D31/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 NameOperation StatusTargetOnboarding DateCap ACap BCap CCap D
A1ActiveABC30/12/202212/12/202131/12/202025/10/202013/06/2022
A2ActiveCCC10/01/202528/12/202228/12/202215/12/202031/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

Labels (2)
1 Solution

Accepted Solutions
JordyWegman
Partner
Partner

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:

2021-07-31 20_00_58-QlikView x64 - [C__Users_cljwn_Downloads_comm116844.qvw_].png

Jordy

Climber

 

Work smarter, not harder

View solution in original post

2 Replies
JordyWegman
Partner
Partner

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:

2021-07-31 20_00_58-QlikView x64 - [C__Users_cljwn_Downloads_comm116844.qvw_].png

Jordy

Climber

 

Work smarter, not harder

View solution in original post

lalita_sharma
Contributor III
Contributor III
Author

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