Wednesday 15 February 2012

Excluding Zeros in Set Analysis

I've been reading a lot of historic posts on the QlikCommunity (yes I need to get out more) and came across an interesting question, how to exclude zeros when performing an average calculation in an expression. The offered solution is a noteworthy snippet:

avg({< Value -= {0} >} Value)

This is a perfect solution but it got me thinking about the difference between "-=" and "=-". The former will take current selections in the Value field and then remove zeros. The latter will ignore current selection in the Value field and remove zeros from all possible values. To avoid the confusion you could use the following syntax instead:

avg({$-< Value = {0} >} Value)

This method would give the same answer but be careful if you try to add other selections to the set modifier as you would remove more data along with the zeros.

1 comment:

  1. Some example of Set Analysis. Could be helpful
    http://community.qlikview.com/docs/DOC-4350
    http://community.qlikview.com/docs/DOC-4275
    http://community.qlikview.com/docs/DOC-4386

    ReplyDelete