Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Announcements

YOUR OPINION MATTERS! Please take the Qlik Experience survey you received via email. Survey ends June 14.

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- SUM Qty (Count Distinct Orders w/ Set Analysis)

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

gramqlik

Contributor III

2018-03-16
10:34 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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,957 Views

1 Solution

Accepted Solutions

gramqlik

Contributor III

2018-03-19
07:14 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

1,832 Views

4 Replies

sunny_talwar

MVP

2018-03-16
10:37 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Try this may be

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

1,832 Views

gramqlik

Contributor III

2018-03-19
04:40 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

1,832 Views

sunny_talwar

MVP

2018-03-19
04:52 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

May be this

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

1,832 Views

gramqlik

Contributor III

2018-03-19
07:14 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

1,833 Views