topic Calculated dimesion in Pivot in QlikView

Calculated dimesion in Pivot

Wed, 29 Jul 2009 19:50:13 GMT

Hi,

I am creating a pivot table using columns 'Year' and expression 'Sum(Sales)'. Year has values (2007,2008,2009). I just need to show 2007 and 2009 sales so pivot will look like:

2007 2008

Sum(Sales) 1300 2000

I cannot apply a where condition in Script as in other pivot i need to show 2009 data also.

So need to create a calculated dimension on files 'Year' to show sales data for 2007 and 2008.

Please help

Thanks,

Sunil

Calculated dimesion in Pivot

avastani — Wed, 29 Jul 2009 20:15:50 GMT

why not use Set Analysis for all years before current year?

Calculated dimesion in Pivot

Wed, 29 Jul 2009 21:03:06 GMT

Set Analysis will apply on expressions, not on dimension variable.

Calculated dimesion in Pivot

avastani — Wed, 29 Jul 2009 21:18:28 GMT

true that, then how about you create a duplication year in your calendar table and put a null for current year and a the year value otherwise.

then use that as your dimension and suppress where values are null.

Calculated dimesion in Pivot

Anonymous — Thu, 30 Jul 2009 10:24:05 GMT

Why worry about the dimension?

If you use set analysis on the expression to only calculate results for 2007 and 2009:

Sum({$<Year={2007,2009}>} Sales)

Since there is no result for Year=2008 it will not show by default in the pivot since Suppress Zero-Values is checked. Of course you don't have to hard code the years either in the expression but can use Year(Today()) and addmonths(Year(Today()),-24) or something else that makes it dynamic.