topic Set Analysis and Nulls in QlikView

Set Analysis and Nulls

AndrewMcIlwrick — Wed, 02 Jun 2010 16:47:15 GMT

In my report my set analysis expression for one pair of 'year' fields works perfectly, e.g. compare 2010 vs. 2009

SUM({$<SalesYear=CheckYear>} SalesVol)

I would like to be able to provide a facility to compare both a month and year e.g. Jan2010 vs. Mar2010. For this I used

SUM({$<SalesYear=CheckYear,SalesMonth=CheckMonth>} SalesVol)

This works fine when a year and month are both selected. However, it is very restrictive as I can no longer perform
the above 2010 vs. 2009 analysis because the month would be left blank.

I tried modifying the expression to incorporate an IF statement with IsNull logic but the set analysis syntax doesn't seem to
allow this.

Does anyone know if there is an easy way to do this without the use of variables or field content lists ? My example above is simplified from the final version where I would also like to provide similar fields for quarters, weeks and individual date comparisons !

Kind Regards
Andrew.

Set Analysis and Nulls

Wed, 02 Jun 2010 17:06:08 GMT

can you try an empty bracket to represent the null []

in my trials I had to bracket the comparison values, e.g.

SalesMonth={CheckMonth,[]}

to make it work

Regards

Juerg

Set Analysis and Nulls

Wed, 02 Jun 2010 17:07:10 GMT

You could try adding something into your expression to check if a month has been selected, so something like

IF(GetSelectedCount(CheckMonth)>0 , SUM({$<SalesYear=CheckYear,SalesMonth=CheckMonth>} SalesVol) , SUM({$<SalesYear=CheckYear>} SalesVol) )