topic SUM Qty (Count Distinct Orders w/ Set Analysis) in QlikView

SUM Qty (Count Distinct Orders w/ Set Analysis)

gramqlik — Fri, 16 Mar 2018 14:34:29 GMT

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?

Re: SUM Qty (Count Distinct Orders w/ Set Analysis)

sunny_talwar — Fri, 16 Mar 2018 14:37:29 GMT

Try this may be

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

Re: SUM Qty (Count Distinct Orders w/ Set Analysis)

gramqlik — Mon, 19 Mar 2018 08:40:24 GMT

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

Re: SUM Qty (Count Distinct Orders w/ Set Analysis)

sunny_talwar — Mon, 19 Mar 2018 08:52:17 GMT

May be this

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

Re: SUM Qty (Count Distinct Orders w/ Set Analysis)

gramqlik — Mon, 19 Mar 2018 11:14:41 GMT

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.