Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

count + aggr + sum ?

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

1 Solution

Accepted Solutions
whiteline
Honored Contributor II

Re: count + aggr + sum ?

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".

5 Replies
mwoolf
Honored Contributor II

Re: count + aggr + sum ?

In load script:

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

In gauge chart use Sum(Flag)

whiteline
Honored Contributor II

Re: count + aggr + sum ?

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)

Not applicable

Re: count + aggr + sum ?

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

whiteline
Honored Contributor II

Re: count + aggr + sum ?

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".

Not applicable

Re: count + aggr + sum ?

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

Community Browser