topic Set Analysis Help in QlikView

Set Analysis Help

Mon, 28 Mar 2011 17:53:20 GMT

Hi guys,

I am having a small issue with the use of Avg function in a set analysis expression. I have an expression that looks for the average balance as shown below

= avg({<STATUS={'Sold'}, CUSTOMER_NO-={''}>} BALANCE)

The problem is the value returned is just too small and it is not what I expect.

When I do a manual calculation of the avg Balance by doing a sum of all the values and dividing by the total number of records, I get a much higher value which is the correct value.

How can I get the average function to work correctly and return the right value.

Thank you

Set Analysis Help

Anonymous — Mon, 28 Mar 2011 18:15:34 GMT

I don't see anything wrong in your expression.
What do you get if you use

=avg(BALANCE)

and select 'Sold' in Status?

Set Analysis Help

Mon, 28 Mar 2011 20:37:24 GMT

Hi Dennis,

Doing the average that way seems to work fine, but I need those selections in the set analysis piece to stay in instead of having to click on them or set triggers to activate them as I have a lot of avergaes to calculate and setting triggers won't be ideal for my case.

Any more thoughts?

Set Analysis Help

Mon, 28 Mar 2011 20:47:56 GMT

I tend to use dollar signs and double quote marks for my set analysis, so;

= avg({$<STATUS={"Sold"}, CUSTOMER_NO-={""}>} BALANCE)

Thansk

Lewis

Set Analysis Help

erichshiino — Sat, 16 Apr 2011 04:00:18 GMT

Hi,

What are you trying to elinate using CUSTOMER_NO-={''} ? There may be other ways to construct.

Since I don't know your table, maybe your avg get more lines when you do the average.

Try sum({<STATUS={'Sold'}, CUSTOMER_NO-={''}>} BALANCE)/ count({<STATUS={'Sold'}, CUSTOMER_NO-={''}>} ID)

(I used ID because I don't know what key you can use)