topic Re: function in function in set analysis in QlikView

function in function in set analysis

Anonymous — Sat, 17 Oct 2015 11:45:26 GMT

Hello friends

I want to count rows that avg(A-B)<=0 with set analysis like bellow:

count({<Avg(A-B)={"<=0"}>} Avg(A-B))

Please correct my expression

Thanks

Re: function in function in set analysis

swuehl — Sat, 17 Oct 2015 12:21:06 GMT

Average is an aggregation function, and I am unclear how you want to use this on a record base.

In general, you need a field name, no function allowed, left from the equal sign in a set modifier.

I am still unclear what you are trying to achieve, so please post some sample data records and your expected outcome.

Re: function in function in set analysis

Anonymous — Sat, 17 Oct 2015 15:38:33 GMT

SO how i can write my instruction

i wanna get average of column and count rows if their average are less than zero

‌thank you swuehl for help

Re: function in function in set analysis

petter — Sat, 17 Oct 2015 16:16:09 GMT

=Sum( Aggr( -(Avg(A-B)<0) , Group ) )

Group is the dimension that you want an average for. The comparison Avg(A-B)<0 will return -1 or 0 for true or false and the -1 we can invert the sign so we get a 1 which is easy to do a Sum which is essentially then a count...

Re: function in function in set analysis

petter — Sat, 17 Oct 2015 16:16:57 GMT

Here is a demo:

Re: function in function in set analysis

Anonymous — Mon, 19 Oct 2015 07:44:59 GMT

Hi Petter! Thanks for taking time to answer.