Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hello,
You can use this :
=rangesum(aggr(NODISTINCT sum(Value),Vendor))
Martin
Hello,
You can use this :
=rangesum(aggr(NODISTINCT sum(Value),Vendor))
Martin
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 ?
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.
Why collapsing a dimension changes the behaviour of the "sum" ?
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