topic SV:Aggr with If help needed in QlikView

Aggr with If help needed

Thu, 11 Nov 2010 15:37:51 GMT

Hi,

I am trying to sum the number of calls in a job if the sum of the number of answer machines is 0. The formula that I have at the moment is

sum(if(aggr(sum(AnsMachine),JobNumber)>0,0,aggr(sum(Calls),JobNumber)))

but this is incorrect. The first point of this sums the answer machines, grouped by job, then checks if this is greater than 0 and if it is sets the result to 0, if the sum is 0 then is sums the calls over the same job numbers.

Any ideas where I am going wrong? I have attached a sample qvd which should help explain.

Aggr with If help needed

Thu, 11 Nov 2010 15:54:14 GMT

Hi.

test

sum(if(aggr(sum(AnsMachine),JobNumber)=0,aggr(sum(Calls),JobNumber),0))

Regards

Anders

SV:Aggr with If help needed

gandalfgray — Thu, 11 Nov 2010 16:00:28 GMT

or you can try:


if(aggr(sum(AnsMachine),JobNumber)=0, aggr(sum(Calls),JobNumber), 0)

SV:Aggr with If help needed

Thu, 11 Nov 2010 16:21:26 GMT

I have found that performance takes a hit the more IF statements you have within the sum. However, Sum (if x=y,y) seems to run better than if(x=y,sum(y)). They don't work exacly the same way.

Aggr with If help needed

Thu, 11 Nov 2010 17:13:15 GMT

Hi All,

Thanks for the help. Although not exactly what I wanted I waas able to use the different suggestions to get to the answer (although I'm not totally sure why it is the answer. The final solution is below.

sum(aggr(if(aggr(sum(AnsMachine),JobNumber)=0,aggr(sum(Calls),JobNumber),aggr(sum(AnsMachine*0.1)+sum(Calls),JobNumber)),JobNumber))

You may notice that it is a little more complicated than the original, where the sum of answer machines is 0 I want one formula, where it is not 0 I want another.

Thanks for you help.