Problem with simple totals

[#1]

Hi !

I have a problem when I want to compute totals in my graph.

Let's say I have the following hierachy :

World --> Continent --> Country --> City --> Vendor

and "value".

I want to display a chart with the sum of "value", and the total by vendor.

I make a column "=sum(Value)" and another "=sum(total <Vendor> Value)".

This gives me this :

I notice that my totals are OK.

"Ted" have a total of 15 and "Bob" have a total of 123.

BUT, when I collapse a dimension, my total becomes wrong !

"Ted" have a total of 10 and "Bob" have a total of 102.

How can I do to have the correct value (123 for Bob) ?

I tried "=aggr(sum(Value), Vendor)" but it doesn't work.

[martin59 · Accepted solution]

Hello,

You can use this :

=rangesum(aggr(NODISTINCT sum(Value),Vendor))

Martin

[#3]

Wow !

It gives me what I want !

I've never used the "nodistinct" keyword. Even if I read the QV manual, I don't really understand what it does...

Could you please explain me what it does ?

[martin59]

The qualifier NODISTINCT allows to calculate the aggregate even if the dimension returns several values.

So when you collapse a dimension, your aggregate return more than one value. That's why I used NODISTINCT.

[#5]

Why collapsing a dimension changes the behaviour of the "sum" ?

[martin59]

When you collapse a dimension and have several vendors, you can't show the value of only one vendor.

To counter that, you can do this :

if(Count(Distinct Vendor)>1,
 'Several vendors : No value',
 rangesum(aggr(NODISTINCT sum(Value),Vendor)))

or

if(Count(Distinct Vendor)>1,
 sum(Value),
 rangesum(aggr(NODISTINCT sum(Value),Vendor)))

but take care with this option, it's may be complicated for the end user.

Martin