Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
@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 ?
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.