Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
sangeess21
Creator
Creator

Avoid counting duplicate values in straight table

Hi all,

I have a straight table where it shows duplicate values. I would like to count the ordered quantity only once for each order. I used expression sum(Ordered Quantity) for Ordered Quantity and it is counting the duplicate values too.

In the below example, order 15562 has three distinct block codes and hence shows the ordered quantity thrice. So, when I use the expression sum([Ordered Quantity]) it shows sum as 3 whereas we have only one ordered quantity for that order. How can I make the Ordered quantity to show 1 for the below order. Any ideas?

Thanks!

1 Solution

Accepted Solutions
sunny_talwar

More like this

Sum(Aggr([Ordered Quantity], [Order Number]))

or

Sum(Aggr(Only([Ordered Quantity]), [Order Number]))

or

Sum(Aggr(Sum(DISTINCT [Ordered Quantity]), [Order Number]))

or

Sum(Aggr(Avg([Ordered Quantity]), [Order Number]))

View solution in original post

6 Replies
Anil_Babu_Samineni

May be this?

Sum(Aggr(sum([Ordered Quantity]), [Order Number]))

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
sunny_talwar

More like this

Sum(Aggr([Ordered Quantity], [Order Number]))

or

Sum(Aggr(Only([Ordered Quantity]), [Order Number]))

or

Sum(Aggr(Sum(DISTINCT [Ordered Quantity]), [Order Number]))

or

Sum(Aggr(Avg([Ordered Quantity]), [Order Number]))

sangeess21
Creator
Creator
Author

Thanks to both sides of you. Sunny's expression did the trick for me

effinty2112
Master
Master

Hi Sangeetha,

If you still want to show your value in a straight table then maybe:

Order Date Order Number Order Line Block Code Sum(Aggr(only([Ordered Quantity]), [Order Number],[Order Line]))
3
09/05/201715592190MISC1
09/05/201715592190PRICE0
09/05/201715592190SHIP TO0
09/05/201715592191MISC1
09/05/201715592191PRICE0
09/05/201715592191SHIP TO0
09/05/201715593190MISC1
09/05/201715593190PRICE0
09/05/201715593190SHIP TO0

I added a line to your order and a new order too to show the effect of more data.

Regards

Andrew

its_anandrjs

You have different order dates and different Block code also for the Quantity so it is shown 1 and total should be 3  for each quantity where else try some of this

Sum(Aggr(Sum(DISTINCT [Ordered Quantity]), [Order Number]))


Or


Count(Aggr(Sum(DISTINCT [Ordered Quantity]), [Order Number]))

edmondo_tassi
Contributor III
Contributor III

I dont sure to understand, have you think to use also dimensionality() function as possibility to differentiate total in different lier of data?