topic Filtering particular values in a dimension - pivot Table in QlikView

Filtering particular values in a dimension - pivot Table

Anonymous — Thu, 11 Feb 2010 10:12:57 GMT

Hi all,

I am having a criteria to filter the zone 1 only in my pivot table from zone dimension.I attached the excel sheet data and expected output for the same.Can anyone suggest the way to complete it.

Input table for the pivot table:

Expected pivot table output:

Filtering particular values in a dimension - pivot Table

johnw — Fri, 12 Feb 2010 00:19:00 GMT

So you want the pivot table to show only Zone 1? Perhaps use a calculated dimension and suppress when null?

if(Zone='Zone 1',Zone)

Filtering particular values in a dimension - pivot Table

blaise — Fri, 12 Feb 2010 01:33:52 GMT

Or if your expressions allows move the if statement inside the expression rather then making a calculated expression (better performance as John earlier stated in another post).

F.ex. If your expression is sum(Revenue) make sum(if(Zone='Zone 1',Revenue,null()) and supress null on Zone dimension.

An ever better solution is to move the if statement to the script.

Filtering particular values in a dimension - pivot Table

johnw — Fri, 12 Feb 2010 01:45:20 GMT

Good points. I think you're right that the best solution is to move it into the script. In the same table that has the Zone field, add a new field:

if(Zone='Zone 1',Zone) as "Zone 1",

Then use "Zone 1" as your dimension instead of Zone, and suppress null values.