topic Calculated Dimension to be used? in QlikView

Calculated Dimension to be used?

Sun, 13 Nov 2011 21:21:49 GMT

I have two intentory files, a file that contains master info (item #, desc, product line and category) and a file that contains qty info(warehouse, qty, ave price)

I have joined them by the item number and have created a straight table and a pivot table. The pivot table has product line and category as dimensions. I would like to limit the product lines displayed without using a list box (i.e. always omit TOOL and SUPP) can I use a calculated dimension to do this.

I also have a straight table listing the items selected. Unfortunately it is also listing the items without an onhand quantity. How can I limit this? I have attached a copy of the object.

Thanks in advance for any assistance given.

Calculated Dimension to be used?

swuehl — Mon, 14 Nov 2011 00:11:24 GMT

Hi,

for limiting the dimension, you have to possibilities:

- either use a calculated dimension like

=if(not match(ProductLine,'TOOL','SUPP'),ProductLine)

- or use a set expression in (all of) your expressions, e.g.

=sum({<ProductLine -= {SUPP,TOOL} >}QtyOnHand)

(please note that you can't use any of these expressions in a table box, that's the object you put in your sample file, not a pivot table)

In the straight table, you won't get any rows with zero onhand qty and its related sum(QtyOnHand * IM2_AveCost), if suppress zero values in presentation tab is checked (and it was checked in the sample and I haven't noticed any lines without onhand quantity). Maybe I misunderstood your question, could you point me to a row which is showing the issue?

Regards,

Stefan

Calculated Dimension to be used?

Mon, 14 Nov 2011 01:09:50 GMT

swuehl,

Thanks for the solution. As far as the straight table, I checked the box in the Presentation tab - Omit if field is null, and that solved the issue. Once again thanks for your help.