topic Re. :Problem with simple totals in QlikView

Problem with simple totals

Wed, 05 May 2010 13:14:47 GMT

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 — Wed, 05 May 2010 13:51:07 GMT

Hello,

You can use this :

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

Martin

Wed, 05 May 2010 15:13:14 GMT

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 — Wed, 05 May 2010 15:46:30 GMT

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.

Wed, 05 May 2010 17:11:28 GMT

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

martin59 — Wed, 05 May 2010 20:08:11 GMT

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)))


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