Monday, 25 January 2010

More on "Sets =- or -=???"

A while ago I blogged about using =- or -= in a set.

I answered a post in QlikCommunity today (http://community.qlikview.com/forums/p/24894/95176.aspx#95176) that demonstrates the use very well.

The question was from sebagiar. He asked how to get an average out of values while ignoring 0s. For example if I have 3 values: 100, 0, 200 then he wants a result of 150, not the 100 that the straight Avg function will give.

My response is to use the following set on the field:

=Avg({< F1 -= {0}>} F1)

So, why "-=" instead of "=-"? Well, the later just tells the field to use all values except 0 - it will exclude any selections on that field. The first one is "additive" (well, I guess I should say "subtractive") and will include and selections on that field and then drop out the zeros.

2 comments:

  1. Thanks Stephen, I will to follow your blog too.

    ReplyDelete
  2. Hi Stephen. I'm new QlikView so please tell Me about Set Analysis...

    ReplyDelete