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

[chriscools]

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

[Gysbert_Wassenaar · Accepted solution]

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.

[chriscools]

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