How can I get the category that comes first

[Julie_]

Hello,

I need a measure where I get the sum of duration for a category which comes first.

For example: 
I have a ticket system where the i have the ticket status: transmitted -> in process -> waiting for answer (when there is something unclear and the processor asks a question) -> solution

I need the sum of duration for either the ticket status "waiting for answer" or "solution", depending on wich category comes first.

I have also a column (Event_number), which sorts the ticketstatus.

Thanks!

[FreemanZ]

hi @Julie_ 

what sample data do you have?

[Julie_]

Either sum the duration for Ticket Status 20 or 35, depending on which Ticket status comes first.

In that case i would need the cumulative sum up to TicketStatus 20, but I have to also sum it with all case_ids.

[FreemanZ]

hi @Julie_ 

could you expand your sample data to include at least two ids?

[Julie_]

the activity_duration is just the duration in milliseconds

[FreemanZ · Accepted solution]

hi @Julie_ 

supposing you have a table like:

case_id	TicketStatus	event_number	activity_duration
1	0	1	101
1	10	2	102
1	15	3	103
1	20	4	104
1	30	5	105
1	35	6	106
1	45	7	107
2	0	1	108
2	10	2	109
2	15	3	110
2	35	4	111
2	30	5	112
2	20	6	113
2	45	7	114

try to plot a measure like:

Measure = 
SUMX(
    VALUES(data[case_id]),
    CALCULATE(
        MINX(
            TOPN(
                1,
                FILTER(
                    data,
                    data[TicketStatus] IN {20, 35}
                ),
                data[event_number],
                ASC
            ),
            data[activity_duration]
        )
    )
)

it worked like:

[Julie_]

is there a possibility to just get the first of either ticket status 20 or 35?

and calculate then the cumulative sum till the first ticket status (20 or 35)