Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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]))

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
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?