Calculating a number of all data but limited to values specific for the dimension

[JurgenW]

Hi,

I've been trying all day to calculate a KPI, but without succes. If anybody can help...

I work for a transport company and need to calculate per customer how many units we deliver at the addresses were we deliver for this customer, regardless whether these units are sent by the same customer.

An example: At a certain moment customer A asks us to deliver 1 unit at address X and 2 at address Y. At the same time another customer B also asks us to deliver 1 unit at address X.

So for customer A we need to make 2 stops and during those 2 stops we delivered 4 units (3 from him and one from another customer)

So our 'number of units per stop' for this customer is 2 (4 units / 2 stops)

When I select a single customer this formula

=Sum({$<Customer=,Stop=P(Stop)>}Units)/Count(Distinct Stop)

works correct when I put it in a text object.

But when I put this formula in a chart to get a list of customers  and there 'number of units per stop', It only counts the units of the customer itself.

I've tried using aggr(), P(), TOTAL in several combinations and sometimes I get results that resemble what I need, but never a correct result.

In attachment a small QVW which demonstrates the issue.

Any help would be greatly appreciated

[tresesco · Accepted solution]

May be this?

Sum( Aggr(Sum(total <Stop> Units),Stop,Customer))

[JurgenW]

Hallo Tresesco

Thank you very much. Your suggestion put me on the right track. I never thought about using the 'Total <Stop>'.

I only had to make two small adjustments to make it work for me.

First I had to add 'Customer=' in the inner sum so the result wouldn't change if I selected one or more customers.

Secondly I had to add '{<Stop=P(Stop)>}' to the outer sum because sometimes I got to many units counted. I suspect this has something to do with shipments where Stop is null.

So the final result is this

=Sum({<Stop=P(Stop)>}Aggr(Sum({<Customer=>}Total <Stop> Units), Stop, Customer))