topic Re: Avoid counting duplicate values in straight table in QlikView

Avoid counting duplicate values in straight table

sangeess21 — Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Avoid counting duplicate values in straight table

Anil_Babu_Samineni — Thu, 07 Sep 2017 15:30:00 GMT

May be this?

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

Re: Avoid counting duplicate values in straight table

sunny_talwar — Thu, 07 Sep 2017 15:32:05 GMT

Re: Avoid counting duplicate values in straight table

sangeess21 — Thu, 07 Sep 2017 15:46:05 GMT

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

Re: Avoid counting duplicate values in straight table

effinty2112 — Thu, 07 Sep 2017 15:47:11 GMT

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/2017	15592	190	MISC	1
09/05/2017	15592	190	PRICE	0
09/05/2017	15592	190	SHIP TO	0
09/05/2017	15592	191	MISC	1
09/05/2017	15592	191	PRICE	0
09/05/2017	15592	191	SHIP TO	0
09/05/2017	15593	190	MISC	1
09/05/2017	15593	190	PRICE	0
09/05/2017	15593	190	SHIP TO	0

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

Regards

Andrew

Re: Avoid counting duplicate values in straight table

its_anandrjs — Thu, 07 Sep 2017 15:48:30 GMT

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

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

Re: Avoid counting duplicate values in straight table

edmondo_tassi — Thu, 07 Sep 2017 15:51:23 GMT

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