Counting data

[#1]

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

[pover · Accepted solution]

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.

[johnw]

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))

[#4]

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