Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
Try this may be
=Sum(DISTINCT {<[Shipped] = {"Null"}, [DueDate] = {"Null"}, Flag = {1} >} [Order Qty])
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
May be this
=Sum({<[Shipped] = {"Null"}, [DueDate] = {"Null"}, Flag = {1}>} Aggr(Sum(DISTINCT {<[Shipped] = {"Null"}, [DueDate] = {"Null"}, Flag = {1}>} [Order Qty]), ORDERLINEID))
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.