topic Fixed expression to calculate averages in QlikView

Fixed expression to calculate averages

Dayna — Fri, 11 Jun 2010 10:25:34 GMT

Hello All!

Hope you can help me, I have a chart that shows me all products that were less than the average using the following expression:

(avg(TOTAL <[Product Code]> [Sale Price] )>=sum([Sale Price]),sum([Sale Price]))

But QlikView obivously works where if you select something the expression will dynamically change, i.e. if i select one product the average would be the sale price of that product, as there is nothing to compare it to - whereas I want it to compare to the average for that product for all customers and orders.... any ideas?

I would really appreciate anything you can do to help!!

Kind Regards,
Dayna

Fixed expression to calculate averages

johnw — Fri, 11 Jun 2010 23:46:40 GMT

Maybe add {1} to it to tell it you want it to ignore all selections?

if(avg({1} TOTAL <[Product Code]> [Sale Price] )>=sum([Sale Price]),sum([Sale Price]))

Or maybe like this if you're earlier than version 8.5. Well, it should behave the same in 8.5 and 9.0 as well, but is no longer officially supported as best I can tell:

if(avg(ALL <[Product Code]> [Sale Price] )>=sum([Sale Price]),sum([Sale Price]))

Fixed expression to calculate averages

Dayna — Mon, 14 Jun 2010 10:21:39 GMT

Hello John,

That is perfect for the code! However, my slight issue now is that we have 200+ customers and obviously it shows them all... How could I get the expression to calculate with everything, but only show the appropriate customers (i.e. pick North Customers and only see the relevant customers rather than all...)

Many thanks John!

Kind Regards,
Dayna

Fixed expression to calculate averages

johnw — Mon, 14 Jun 2010 17:21:16 GMT

Well, how best to do it depends on exactly which selections you want to pay attention to, and which you want to ignore. At this point, all I think I know is that you want to pay attention to customers, but NOT to product codes.

I think this would work for "ignore ONLY product code selections":

if(avg({<[Product Code]=>} TOTAL <[Product Code]> [Sale Price] )>=sum([Sale Price]),sum([Sale Price]))

I think this would work for "ignore all selections EXCEPT for customer" (in 9.0, anyway. P(field) doesn't work in 8.5):

if(avg({1<[Customer]=P([Customer])>} TOTAL <[Product Code]> [Sale Price] )>=sum([Sale Price]),sum([Sale Price]))

In any case, you have two basic possiblities that I know of. You can either start with NO selections {1} and then explicitly tell it field=P(field) for each field you want to use selections for. Or you can start with ALL selections (not specifying {1}) and then explicitly tell it which selections to ignore with field=.

Fixed expression to calculate averages

Dayna — Tue, 15 Jun 2010 09:52:29 GMT

That's great, thank you very much for your help, John. I'll have a play and see what I get!

Kind Regards,
Dayna