Aggr not working in this example

[#1]

Hi everyone,

I am struggling with an aggr expression for a dimension in a table.

=aggr(IF(sum(

((count({<TeleAdviceFlag={1},EmergencyIncidents={1}>}distinct(IncidentNumber))

+

(count({<NullTimeFirstUnitAtHospitalFlag={1},TeleAdviceFlag=-{1},EmergencyIncidents={1}>}distinct(IncidentNumber)))

+

(sum({<TimeArriveHospitalFlag= {1},responding_priority= {'0','1','2','3','4','5','6'},TeleAdviceFlag= -{1},SLA_Conveyance_Type12={1}>}EmergencyIncidents))

+

(sum({<TimeArriveHospitalFlag= {1},responding_priority= {'0','1','2','3','4','5','6'},TeleAdviceFlag= -{1},SLA_Conveyance_TypeOther={1}>}EmergencyIncidents)))

-

((Sum({<responding_priority={'0', '1','2','3','4','5','6'}>} EmergencyIncidents)))))>0,IncidentNumber), IncidentNumber)

The idea is that it will count and sum the above and if the total is not zero (the above says greater then zero...) then show the incident number. Instead it is showing nothing. Is anyone able to see what is wrong?

[bgerchikov]

Hi Phil,

Your IF statement supposed to result some number, correct? Let's say it's 1. So, what we have in aggr function then?

=aggr(IF(1>0,IncidentNumber), IncidentNumber) ===>  same as aggr(IncidentNumber),

I don't think it's gonna work...

Try to use RangeSum function:

=(if(rangesum(

((count({<TeleAdviceFlag={1},EmergencyIncidents={1}>}distinct(IncidentNumber))

,

(count({<NullTimeFirstUnitAtHospitalFlag={1},TeleAdviceFlag=-{1},EmergencyIncidents={1}>}distinct(IncidentNumber)))

,

(sum({<TimeArriveHospitalFlag= {1},responding_priority= {'0','1','2','3','4','5','6'},TeleAdviceFlag= -{1},SLA_Conveyance_Type12={1}>}EmergencyIncidents))

,

(sum({<TimeArriveHospitalFlag= {1},responding_priority= {'0','1','2','3','4','5','6'},TeleAdviceFlag= -{1},SLA_Conveyance_TypeOther={1}>}EmergencyIncidents)))

,

-1*((Sum({<responding_priority={'0', '1','2','3','4','5','6'}>} EmergencyIncidents)))))>0,IncidentNumber), IncidentNumber)

Doublecheck "(" and ")" as well

[#3]

Hi Boris, thanks for the help. This is what I have adapted

=aggr(IF(rangesum(count({<TeleAdviceFlag={1},EmergencyIncidents={1}>}distinct(IncidentNumber))

,

count({<NullTimeFirstUnitAtHospitalFlag={1},TeleAdviceFlag=-{1},EmergencyIncidents={1}>}distinct(IncidentNumber))

,

sum({<TimeArriveHospitalFlag= {1},responding_priority= {'0','1','2','3','4','5','6'},TeleAdviceFlag= -{1},SLA_Conveyance_Type12={1}>}EmergencyIncidents)

,

sum({<TimeArriveHospitalFlag= {1},responding_priority= {'0','1','2','3','4','5','6'},TeleAdviceFlag= -{1},SLA_Conveyance_TypeOther={1}>}EmergencyIncidents)

,

-1*(Sum({<responding_priority={'0', '1','2','3','4','5','6'}>} EmergencyIncidents)))<>0,IncidentNumber), IncidentNumber)

I had to amend the brackets slightly and include the aggr at thr front but it does give the incident number if the sum of the expressions are not zero.

Many thanks

[#4]

Hi Boris, thanks for the help. This is what I have adapted

=aggr(IF(rangesum(count({<TeleAdviceFlag={1},EmergencyIncidents={1}>}distinct(IncidentNumber))

,

count({<NullTimeFirstUnitAtHospitalFlag={1},TeleAdviceFlag=-{1},EmergencyIncidents={1}>}distinct(IncidentNumber))

,

sum({<TimeArriveHospitalFlag= {1},responding_priority= {'0','1','2','3','4','5','6'},TeleAdviceFlag= -{1},SLA_Conveyance_Type12={1}>}EmergencyIncidents)

,

sum({<TimeArriveHospitalFlag= {1},responding_priority= {'0','1','2','3','4','5','6'},TeleAdviceFlag= -{1},SLA_Conveyance_TypeOther={1}>}EmergencyIncidents)

,

-1*(Sum({<responding_priority={'0', '1','2','3','4','5','6'}>} EmergencyIncidents)))<>0,IncidentNumber), IncidentNumber)

I had to amend the brackets slightly and include the aggr at thr front but it does give the incident number if the sum of the expressions are not zero.

Many thanks

[#5]

Hi Boris, thanks for the help. This is what I have adapted

=aggr(IF(rangesum(count({<TeleAdviceFlag={1},EmergencyIncidents={1}>}distinct(IncidentNumber))

,

count({<NullTimeFirstUnitAtHospitalFlag={1},TeleAdviceFlag=-{1},EmergencyIncidents={1}>}distinct(IncidentNumber))

,

sum({<TimeArriveHospitalFlag= {1},responding_priority= {'0','1','2','3','4','5','6'},TeleAdviceFlag= -{1},SLA_Conveyance_Type12={1}>}EmergencyIncidents)

,

sum({<TimeArriveHospitalFlag= {1},responding_priority= {'0','1','2','3','4','5','6'},TeleAdviceFlag= -{1},SLA_Conveyance_TypeOther={1}>}EmergencyIncidents)

,

-1*(Sum({<responding_priority={'0', '1','2','3','4','5','6'}>} EmergencyIncidents)))<>0,IncidentNumber), IncidentNumber)

I had to amend the brackets slightly and include the aggr at thr front but it does give the incident number if the sum of the expressions are not zero.

Many thanks

[#6]

Hi Boris, thanks for the help. This is what I have adapted

=aggr(IF(rangesum(count({<TeleAdviceFlag={1},EmergencyIncidents={1}>}distinct(IncidentNumber))

,

count({<NullTimeFirstUnitAtHospitalFlag={1},TeleAdviceFlag=-{1},EmergencyIncidents={1}>}distinct(IncidentNumber))

,

sum({<TimeArriveHospitalFlag= {1},responding_priority= {'0','1','2','3','4','5','6'},TeleAdviceFlag= -{1},SLA_Conveyance_Type12={1}>}EmergencyIncidents)

,

sum({<TimeArriveHospitalFlag= {1},responding_priority= {'0','1','2','3','4','5','6'},TeleAdviceFlag= -{1},SLA_Conveyance_TypeOther={1}>}EmergencyIncidents)

,

-1*(Sum({<responding_priority={'0', '1','2','3','4','5','6'}>} EmergencyIncidents)))<>0,IncidentNumber), IncidentNumber)

I had to amend the brackets slightly and include the aggr at thr front but it does give the incident number if the sum of the expressions are not zero.

Many thanks

[bgerchikov]

Hi Phil,

Still don't understand aggr function role in your expression. How it works if you don't use it at all?

[#8]

Hi Brois, this is the formula that works for me.

=aggr(IF(rangesum(count({<TeleAdviceFlag={1},EmergencyIncidents=>}distinct(IncidentNumber)) , count({<NullTimeFirstUnitAtHospitalFlag=,TeleAdviceFlag=-,EmergencyIncidents=>}distinct(IncidentNumber))

,

sum({<TimeArriveHospitalFlag= {1},responding_priority= {'0','1','2','3','4','5','6'},TeleAdviceFlag= -,SLA_Conveyance_Type12=>}EmergencyIncidents)

,

sum({<TimeArriveHospitalFlag= {1},responding_priority= {'0','1','2','3','4','5','6'},TeleAdviceFlag= -,SLA_Conveyance_TypeOther=>}EmergencyIncidents)

,

-1*(Sum({<responding_priority={'0', '1','2','3','4','5','6'}>} EmergencyIncidents)))<>0,IncidentNumber), IncidentNumber)