Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
JurgenW
Contributor
Contributor

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

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

 

1 Solution

Accepted Solutions
tresesco
MVP
MVP

May be this?

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

View solution in original post

2 Replies
tresesco
MVP
MVP

May be this?

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

View solution in original post

JurgenW
Contributor
Contributor
Author

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))