topic Aggr not working in this example in QlikView

Aggr not working in this example

Fri, 10 Aug 2012 14:43:28 GMT

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?

Re: Aggr not working in this example

bgerchikov — Fri, 10 Aug 2012 21:01:54 GMT

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

Re: Aggr not working in this example

Wed, 15 Aug 2012 15:36:18 GMT

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

Re: Aggr not working in this example

Wed, 15 Aug 2012 15:36:25 GMT

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

Re: Aggr not working in this example

Wed, 15 Aug 2012 15:36:32 GMT

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

Re: Aggr not working in this example

Wed, 15 Aug 2012 15:38:58 GMT

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

Re: Aggr not working in this example

bgerchikov — Wed, 15 Aug 2012 15:41:49 GMT

Hi Phil,

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

Re: Aggr not working in this example

Wed, 15 Aug 2012 15:45:03 GMT

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)