topic Getting A Count From Multiple Dimensions in QlikView

Getting A Count From Multiple Dimensions

Tue, 13 Jan 2015 10:41:50 GMT

HNY folks...

Got a painful task that's proving to be a bit of a pig to solve...

Basically:

I have a straight table that has 3 dimensions and an expression..

OrderID	Warehouse	Customer	Total Order Units
1	Edinburgh	Smith	77.00
1	Edinburgh	Jones	77.00
1	Edinburgh	Brown	77.00

Ignore the logic of the data, the basic premise is that one order can sit in one warehouse, but have multiple customer names.

The expression total (77.00) is the total order value - what I need to do, is to assume it's a straight split, so basically have the 77 split equally between all the Customers in the order (25.67)...given other orders will exist that have more (or less) customers, I need an expression that will count the customers per order per warehouse and divide the total order value....

Confused ? Welcome to the club.

Thanks in advance....

(Don't ask for a QVW as there isn't one )

Re: Getting A Count From Multiple Dimensions

Clever_Anjos — Tue, 13 Jan 2015 10:52:18 GMT

count(total <OrderId> Customer) will bring you how much customers are in your Order, so you can divide your Total Unit Units by this value

Re: Getting A Count From Multiple Dimensions

kiranmanoharrode — Tue, 13 Jan 2015 10:54:52 GMT

Hi Graeme,

Use AVG() function for above requirement.

=AGGR(AVG(Order_Units),Customer,Warehouse)

Regards,
Kiran

Re: Getting A Count From Multiple Dimensions

Tue, 13 Jan 2015 11:05:53 GMT

Thanks Clever

Nearly - but with a bit of tweaking I got there..

Count(TOTAL <Warehouse>Customer)

Your answer was limited to only that selection (ie OrderID 1 & Warehouse Edinburgh) - when you expanded back out to the full order id history, the count expanded to.

Thanks though - can't believe I missed that