topic Re: count + aggr + sum ? in QlikView

count + aggr + sum ?

Fri, 31 Aug 2012 09:06:55 GMT

Hello everyone,

How are you all doing?

Well, I need a help on that thing

Here are the data :

id; A; B ; C; D

Dwayne; 1512,0,1325, 3125

Derrick; 0,0,1541,3125

Kobey, 0,5264,1254,3125

Dwight;12;45;65;3125

So, I need to count the id which have (A + B +C ) > 30% of D to put in a jauge.

I think, I 'll need something like : aggr(sum(A) ,sum(A),sum(C), id)>(0.3*D)

But I don't really know how to handle that.

I 'll be glad to have your point of view on that question.

Wish you all the best,

Will

Re: count + aggr + sum ?

m_woolf — Fri, 31 Aug 2012 10:56:49 GMT

In load script:

if(A+B+C>.3*D,1,0) as Flag,

In gauge chart use Sum(Flag)

Re: count + aggr + sum ?

whiteline — Fri, 31 Aug 2012 12:59:05 GMT

This can be used in charts to count:

=count({$<id={"=A+B+C>.3*D"}>} id)

To create a listbox of such ids for selection you can use =aggr(Only({1<id={"=A+B+C>.3*D"}>} id), id)

Re: count + aggr + sum ?

Fri, 31 Aug 2012 14:30:05 GMT

I ddn't know that you could do such a thing ( id=A+B+C). I'll keep that in mind. This expression is meant to be use in text object.

I 'll test it right away . Thanks

Re: count + aggr + sum ?

whiteline — Fri, 31 Aug 2012 14:54:53 GMT

That's simple and powerful thing.

It's set analysis (look at help index). Briefly, you modify the set of data that is used to calculate the formula count(id).

{$<id={"=A+B+C>.3*D"}>}

Means: take current selection set (symbol $), and select only those values of ids that have (A+B+C>.3*D) = TRUE.

So this is not "id=A+B+C".

Re: count + aggr + sum ?

Mon, 03 Sep 2012 14:11:58 GMT

Hey,

Your set analysis works great !

So I copy/paste into my app, and it doesn't work at all .....

the picture below show a part of the data.

I 'm pretty sure your solution come close ,but I can't really figure it out ( 3 hours spent so far). And , it was my fault forgeting to mention the month field.

What a challenge...