
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Count Rows where only the last row in a group falls in a specific date range - Pivot Table
I am looking to only count one row per ticket when a date range is specified. The rows must ONLY be counted if the status is 02,06 or 07 between a specific date range and ONE ROW must be counted per ticket if the criteria are met
Ticket | Status | Process Date | Order | <ROW- Not included but to show what I need> |
X | 02 | 01-12-22 | 1 | 1 |
X | 06 | 01-12-22 | 2 | 2 |
X | 07 | 02-12-22 | 3 | 3 |
X | 06 | 03-12-22 | 4 | 4 |
X | 88 | 04-12-22 | 5 | 5 |
Y | 02 | 01-12-22 | 1 | 6 |
Y | 07 | 02-12-22 | 2 | 7 |
Y | 06 | 02-12-22 | 3 | 8 |
Y | 07 | 04-12-22 | 4 | 9 |
Y | 88 | 05-12-22 | 5 | 10 |
e.g.
Process Date Range 02-12 to 03-12 would count rows 4 and 8 and so return a count of 2
Process Date Range 02-12 to 04-12 would count row 9 and so return a count of 1
I tried to use Count(FirstSortedValue()) but was told I can't nest aggregate functions
Is there a way to do this in Qlik ?
Thanks
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
This was finally solved using this function
Sum(Aggr(if(TICKET_ORDER=max({<STATUT={02,06,07}>}Total <DEMANDE_ID> TICKET_ORDER),1,0),DEMANDE_ID,TICKET_ORDER,STATUT))
Essentially find where the ticket_order is the same as the maximum ticket_order for each demande id, then group by the demande_id, ticket_order and statut to get the cumulative total per statut.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
Why don’t you prepare this in the script and give and 0 or 1 when it doesn’t or does comply. Then you could do RangeSum() on the field in the front end.
Jordy
Climber

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks for your answer
The challenge is that the date range is selected from a date picker and so
the compliance will change with each date selection

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@JordyWegman I have managed to make some progress on this, however, I'm still not getting the results I want
Once the dates have been selected
Process Date Range 02-12 to 03-12
Consider the following statement
max( {<$STATUT={02,06,07}>} TOTAL <TICKET> ORDER)
I would expect this to return the maximum ORDER for each ticket, however this returns 4 in every case.
How can I get this to return the MAX(ORDER) for each ticket ?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
This was finally solved using this function
Sum(Aggr(if(TICKET_ORDER=max({<STATUT={02,06,07}>}Total <DEMANDE_ID> TICKET_ORDER),1,0),DEMANDE_ID,TICKET_ORDER,STATUT))
Essentially find where the ticket_order is the same as the maximum ticket_order for each demande id, then group by the demande_id, ticket_order and statut to get the cumulative total per statut.
