Skip to main content
Announcements
YOUR OPINION MATTERS! Please take the Qlik Experience survey you received via email. Survey ends June 14.
cancel
Showing results for 
Search instead for 
Did you mean: 
gramqlik
Contributor III
Contributor III

SUM Qty (Count Distinct Orders w/ Set Analysis)

I know similar questions have been asked a number of times here, but I cannot find a solution that works for me...

I have a Table Object In order to count the number of distinct orders that fit certain criteria.

The Dimension is the Site.

I then have expressions like the following:

=COUNT( DISTINCT {<[Shipped] = {"Null"}, [DueDate] = {"Null"}, Flag = {1} >} ([ORDER_ID]))

Which work as expected.

In another column in the Table Object, I need to calculate the Sum of Order Qty for per site, for all orders that fit the criteria.

So, in Excel, I'd have 5 columns:

Order_ID

Order_Qty

Shipped (filtered to "Null")

DueDate (filtered to "Null")

Flag (filtered to "1")

And the Sum would be

I see that with this set, there are 14 distinct Order_IDs each with a Order_Qty of 100 and one ORDER_ID with a ORDER_QTY of 10. So the value I'm after is 1,410.

So back to the QV Expression, among other things, I tried:

=SUM(AGGR(COUNT(DISTINCT {<[Shipped] = {"Null"}, [DueDate] = {"Null"}, Flag = {1} >} ([ORDER_ID]), ORDER_QTY))

But I keep getting wayward results.

Any ideas?

1 Solution

Accepted Solutions
gramqlik
Contributor III
Contributor III
Author

Gah! Finally found the problem....... there was an error in my expression - I inadvertently the same field twice in the set analysis!

So I had:

{<[Shipped] = {"Null"}, [Shipped] = {"Null"}, Flag = {1} >}

Buit should have had:

{<[Shipped] = {"Null"}, [Due Date] = {"Null"}, Flag = {1} >}

The following works correctly without any DISTINCTs and AGGRs:

=SUM({<[Shipped] = {"Null"}, [Due Date] = {"Null"}, PalletFlag = {1}>} QTY_ORDERED)

Thanks for your help, Sunny.

View solution in original post

4 Replies
sunny_talwar

Try this may be

=Sum(DISTINCT {<[Shipped] = {"Null"}, [DueDate] = {"Null"}, Flag = {1} >} [Order Qty])

gramqlik
Contributor III
Contributor III
Author

The DISTINCT was correct for counting the number of Orders, but there can be multiple records with the same ORDER_ID, but with different Order Qtys. I need to sum all quantities, not just those for distinct orders.

No matter what I try, I still seem to be getting wrong values.

For example, today I have 31 Orders (17 distinct). The Order Qty values for all 31 totals 11,037.

With the SUM DISTINCT you've suggested, it returns 311,977.

If I omit the DISTINCT, it returns 1,180,910.

If I use

=COUNT({<[Shipped] = {"Null"}, [DueDate] = {"Null"}, Flag = {1} >} Order_Qty)

I get 1,622.

If I use

=SUM(AGGR(COUNT({<[Shipped] = {"Null"}, [DueDate] = {"Null"}, Flag = {1} >} ORDER_ID), Order_Qty))

I get 1,036.

....

I found that there is another field, ORDERLINEID, which I've added into my QVW. Say we have:

ORDER_ID = ABC123

ORDERLINEID = 1 , ORDER_QTY = 15

ORDERLINEID = 2 , ORDER_QTY = 10

ORDERLINEID = 3 , ORDER_QTY = 5

ORDER_ID = DEF456

ORDERLINEID = 1 , ORDER_QTY = 20

ORDERLINEID = 2 , ORDER_QTY = 20

So I'm looking for 15 + 10 + 5 + 20 + 20 = 70

sunny_talwar

May be this

=Sum({<[Shipped] = {"Null"}, [DueDate] = {"Null"}, Flag = {1}>} Aggr(Sum(DISTINCT {<[Shipped] = {"Null"}, [DueDate] = {"Null"}, Flag = {1}>} [Order Qty]), ORDERLINEID))

gramqlik
Contributor III
Contributor III
Author

Gah! Finally found the problem....... there was an error in my expression - I inadvertently the same field twice in the set analysis!

So I had:

{<[Shipped] = {"Null"}, [Shipped] = {"Null"}, Flag = {1} >}

Buit should have had:

{<[Shipped] = {"Null"}, [Due Date] = {"Null"}, Flag = {1} >}

The following works correctly without any DISTINCTs and AGGRs:

=SUM({<[Shipped] = {"Null"}, [Due Date] = {"Null"}, PalletFlag = {1}>} QTY_ORDERED)

Thanks for your help, Sunny.