topic Excluding a value from a dimension in QlikView

Excluding a value from a dimension

Tue, 21 Feb 2012 10:49:27 GMT

Should be an easy one, this.

I have a simple chart with a field called [Pipeline Year] as the dimension.

My expression is:

((Sum({< [Pipeline Month]={$(varCurrentMonth)}, COUNTY -={'JSY','GNY','IOM','NHQ'}>} [InCompany Revenue]) +

Sum({< [Pipeline Month]={$(varCurrentMonth)}, COUNTY -={'JSY','GNY','IOM','NHQ'}>} [Scheduled Revenue])))

The problem is that I want to see the month's revenue for the last 5 years. 2012-2008 but because there is money in already in the pipeline for 2013, 2013 is included in the chart. How best is it to exlude 2013 and keep it to 2012-2008?

Do i need to add a caclulated dimension or is there a better way of excluding 2013 (or any future years).

Thanks.

Excluding a value from a dimension

jvitantonio — Tue, 21 Feb 2012 11:03:55 GMT

Hi, you can use IF in youtr expression:

IF( [Pipeline Year] >=2008 AND [Pipeline Year] <=2012,((Sum({< [Pipeline Month]={$(varCurrentMonth)}, COUNTY -={'JSY','GNY','IOM','NHQ'}>} [InCompany Revenue]) +

Sum({< [Pipeline Month]={$(varCurrentMonth)}, COUNTY -={'JSY','GNY','IOM','NHQ'}>} [Scheduled Revenue]))))

Excluding a value from a dimension

Tue, 21 Feb 2012 11:08:31 GMT

Thanks a lot user14, that is just what I needed.