Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Problem with simple totals

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 :

error loading image

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 !

error loading image

"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.

1 Solution

Accepted Solutions
martin59
Specialist II
Specialist II

Hello,

You can use this :

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


Martin

View solution in original post

5 Replies
martin59
Specialist II
Specialist II

Hello,

You can use this :

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


Martin

Not applicable
Author

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
Specialist II
Specialist II

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.

Not applicable
Author

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

martin59
Specialist II
Specialist II

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