topic Problem with expression, values in the rows are correct, but the totals are wrong in QlikView

Problem with expression, values in the rows are correct, but the totals are wrong

chriscools — Sat, 27 Apr 2013 21:47:09 GMT

Hello,

i attach a document i'm working on, and i experience a few strange things:

Table "1"

i calculate the present stock (expression 1), outstanding orders (expression 2),

the amount that is sold and available (expression 3) and % of expression 3 versus expression 1.

problems here are:

expression 4 gives me 18,71%, but if calculate myself the total for expr 3 divided by expr 4 i get 17,13%

=> where does this difference come from and how could i solve that?

Table "2"

This is basically the same as table "1" but without the dimensions, because i want to show this on a "dashboard"-page ,

so i don't want all the details showing there.

problems here are:

-expression 3 gives me the same as expression 2, so i don't have a clue of what is available now of what's on order

-because expression 3 is wrong expression 4 is also totally wrong

=> can these two problems be solved, so i can show these numbers without all the details

Table "3"

Is the same as table "1" but as a pivot table, problems are the same as above.

How come that i can get expression 3 correct in table "1", but not in the pivot version?

The gauge chart is ment to show expression 4, but is also wrong. how can i get it to show the 17,13%?

i included the the file.

Can someone please help me with this?

thanx!

chris

Re: Problem with expression, values in the rows are correct, but the totals are wrong

Gysbert_Wassenaar — Mon, 29 Apr 2013 09:56:36 GMT

Basically, your mixing up sum(if... with if(sum... You're going to have to use the aggr function to create an intermediate virtual table which you can then use compare in an if statement and finally sum the result of that. And to top things off, you need to use the alt function to make sure nulls don't muck up the calculations. See attached example.

Re: Problem with expression, values in the rows are correct, but the totals are wrong

chriscools — Mon, 29 Apr 2013 21:47:53 GMT

Thanx!

now it works ok!

i'll have to study a little bit more on the aggr function and why i mixed the sum if.

Also thank you for the tip regarding the nulls!!

grtz,

chris