Count Rows where only the last row in a group falls in a specific date range - Pivot Table

[clarcombe]

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

[JordyWegman]

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

[clarcombe]

Hello

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

[clarcombe]

@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 ?

[clarcombe · Accepted solution]

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.