Double aggregation + set expression with exceptions for ignoring selection

[ktlnrrrr]

Hi! Asking for help from anyone strong in set analysis.

I have a KPI object with two fields for a single metric. The first, main field shows an average of counts of an event per day and reacts to all selections. The second shows an average of counts per day over all time, ignoring all selections. They both work fine.

Problem 1: I am wondering how I could make the second field (all time average) respond to one selection and ignore all others – so that it would show me the average over all time but according to the selection in variable escalation_type (with values like "first_line", "second_line"). Does anyone know how to add this exception to my set expression?

Problem 2: Similar issue but a different metric: the second, all time average field needs to function in the same way as described above, but contains a more complex expression because of the calculation performed inside. 

My current expressions look like this:

Problem 1. avg({1}aggr(count({1}escalated_at), day))
---
Problem 2: avg({1}aggr(count({1}if(escalation_type = 'first_line',room_id))/count(distinct {1}room_id), day))

Is there anyone who can guide me to the right direction? I have seen how to make exceptions to selection in general, but these examples never contain any double aggregations or calculations, so I don't know how to place the necessary exception conditions into my aggregations. Will appreciate any help!

[sunny_talwar · Accepted solution]

For the first problem you can try one of these expressions

Avg({1} Aggr(Count({1<escalation_type = P(escalation_type)>} escalated_at), day))

or

Avg({1} Aggr(Count({1<escalation_type = $::escalation_type>} escalated_at), day))

For the second problem, I am not sure what exactly are you looking to get modified?

[ktlnrrrr]

Thanks a million, it worked perfectly for the first problem!

For the second one, I am looking to use it in the same way for the outcome of the division. So the following means: if escalation type = first line, then divide count of room_id by count of distinct room_id. The outcome is basically the reiteration rate.

({1}if(escalation_type = 'first_line',room_id))/count(distinct {1}room_id)

Then, the averaging is put around it:

avg({1}aggr(count({1}if(escalation_type = 'first_line',room_id))/count(distinct {1}room_id), day))

 
Would you possibly know where should I insert this condition of ignore all selections except escalation_type when I am dealing with a division inside the expression?

[sunny_talwar · Accepted solution]

Try this

Avg({1} Aggr(
   Count({1<escalation_type = {'first_line'}>} room_id)/Count(DISTINCT {1<escalation_type = p(escalation_type)>} room_id)
, day))

[ktlnrrrr]

Thanks so much, worked like a charm!