set expression to filter on dimension only table

[lalita_sharma]

I have data set  as below.

Workstream	Reporting Date	Name	Description	Completion Date
W1	30/07/2021	A	L	20/05/2021
W1	30/07/2021	B	M	24/05/2021
W1	30/07/2021	C	P	20/05/2021
W1	23/07/2021	A	L	20/05/2021
W1	23/07/2021	B	M	19/05/2021
W1	16/07/2021	A	L	20/05/2021
W1	16/07/2021	B	M	19/05/2021
W2	30/07/2021	D	Q	11/05/2021
W2	30/07/2021	E	R	24/05/2021
W2	30/07/2021	F	S	11/05/2021
W2	23/07/2021	D	Q	11/05/2021
W2	23/07/2021	E	R	19/05/2021
W2	16/07/2021	D	Q	20/05/2021
W2	16/07/2021	E	R	19/05/2021

I want to display this data in a straight table with fields  Name, Description and Completion Date only with below conditions.

When no filter is selected on the filter pane, I want the table to display only records where workstream = W1 and Reporting Date = latest Date. When a filter is selected on the table all records of that Workstream and Reporting Date must be displayed. Could you please let me know how I can achieve this using set expressions. I am confused on how to apply set expression on a dimension only table.

The output should look like below when no filters are selected on filter pane. Thanks

Name	Description	Completion Date
A	L	20/05/2021
B	M	24/05/2021
C	P	20/05/2021

[lalita_sharma]

@sunny_talwar  I have seen excellent answers from you on set expressions in qlik community. Could you please help me with the above query?

[stevejoyce]

You can use a calculated dimension and on the calculated dimension, uncheck Include null values.  Add Name, Description as natural dimensions.  For Date you can have 2 versions but only one at a time will display using "Show column if", you can adjust the condition to what makes sense for the sheet/app.

Completed Date (max date only)

  Calculated dim: =Date(Aggr(max({<Workstream={'W1'}>} [Completion Date]), Name, Description)) //getting max completed date for each Name and Description where workstream = W1

  Show Column If: =not(GetSelectedCount(Description)>0 or GetSelectedCount(Name)>0)

Completed Date (all dates)

  Dim: [Completed Date]

  Show Columnn If: =GetSelectedCount(Description)>0 or GetSelectedCount(Name)>0

[stevejoyce · Accepted solution]

I may have miss-interpreted, i was doing max completed date.  

I'd create a variable called vMaxReportingDate:  =date(max([Reporting Date])) //global calculation of max reporting date based on selections.

The calculated dim would be:  =Aggr(only({<[Reporting Date] = {'$(vMaxReportingDate)'}, Workstream={'W1'}>} [Completion Date]), [Completion Date], Workstream)

[lalita_sharma]

Thank you @Stevejoyce

This worked perfect for me