Limiting a graph's dimension to a certain value

[#1]

Hi everyone,

I'm currently participating in a case study at university and am therefore working myself into the free version of Qlikview . It's actually been quite easy and intuitive so far but there's one thing that I've not managed to do so far:

I have a table that lists all the purchase orders of a company, including fields for the material, its price and the plant that bought it. I now want to calculate the average price of several certain materials for each of the four plants.

So far, I've done this: I have created a chart and used the plant and the material id for dimensions and Avg(price) as the expression. The chart I get lists all the materials that occur in the database. In order to point out certain gaps, I want to limit the material dimension to certain materials. I tried editing the dimension and the first and only thing I came up with was =(MaterialNumber='M321'). Now I get eight bars in my graph, two for each country. The left one shows a value which seems to be the average price of ALL materials combined. The right one displays the average price of material M321. Now how do I get rid of the left one? I thought that an expression like MaterialNumber='M321' would only display the right bar. I could of course do this by adding the material id field as a listbox but upon selecting a material id, some other graphs would get messed up.

Thanks in advance and keep up the good work.

[#2 · Accepted solution]

Hi Tobias

Can you do it by removing the material dimension and use in the expression

avg(if(MaterialNumber='M321'),price))

You could provide the material number in a variable to make it adjust to different materials.

The better way however might be to use a list box for the material and in the charts where you do not want the material limit to show to use a set expression to ignore the material selection.

A simpler method might be to duplicate your material table (new table material-2) and use this copy as the dimension for this specific table?

Regards

Juerg

[#3]

You can't do the limiting on the dimension, it must be on the expression, but you were on the right track.

Dimension should be MATERIAL

Expression should be:

=AVG(IF(MaterialNumber='M321',price))

Good luck,

[#4]

Worked just fine, thanks a lot!