Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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,
Worked just fine, thanks a lot!