Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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)
@sunny_talwar I have seen excellent answers from you on set expressions in qlik community. Could you please help me with the above query?
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
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)
Thank you @Stevejoyce
This worked perfect for me