topic Counting data in QlikView

Counting data

Mon, 11 Oct 2010 17:09:52 GMT

Hi,

May I ask a counting issue as follow:

I have monthly data for 2 columns A and B.

in the table, I check every month:

If A >0

->if A >B then value = 1 else value =2

If A<0

->if A >B then value = -1 else value =-2

I want to find the total numbers of -2, -1, 1, 2 for all time period in Chart (not in script, since B is choosen by users).

How can I write the formula?

regards,

Ivan

Counting data

pover — Mon, 11 Oct 2010 17:32:07 GMT

Ivan,

The easiest solution would be a count(distinct if(A>0 and A>B,Dimension,null())) if A and B are fields, but if A and B formulated then try something like the 4 formulas below:

sum(aggr(if(A>0 and A>B,1,0),Dimensions))

sum(aggr(if(A>0 and A<B,1,0),Dimensions))

sum(aggr(if(A<0 and A<B,1,0),Dimensions))

sum(aggr(if(A<0 and A>B,1,0),Dimensions))

where A and B are the formulas from the columns surrounded by the aggr() function that list the dimensiones of the table at the end of the aggr function. If the formulas con long and complex save them as a variable and call the variable in each formula. These formulas would work in 4 separate text objects.

Regards.

Counting data

johnw — Tue, 12 Oct 2010 00:23:16 GMT

Since you don't specify any value for A=0, I'll assume 0.

sum(aggr(sign(A)*(2+(A>B)),Month))

Or the more readable:

sum(aggr(sign(A)*if(A>B,1,2),Month))

Counting data

Tue, 12 Oct 2010 08:50:22 GMT

Thanks for both Karl and John. It is working fine. cheers!!