topic Calculate a Ratio in QlikView

Calculate a Ratio

darrellbutler — Tue, 19 Apr 2011 13:10:16 GMT

Folks,

I'm trying to work out a service level which is a straightforward calulation of Desired / Delivered. I have a dimension of customer_No.

I now need to exclude some customers who are no longer serviced and I introduced the follwing:

sum({<[CUSTOMER_NO] -= {80148}>}((DELIVERED)/(DESIRED)))

Whilst this works fine and excludes the calculation for the customer in question, all other customers %service level is wrong.i.e 4000%

Any thoughts how I can get round this, using aagr on the customer no dimension does not appear to work either.

Many thanks

Calculate a Ratio

rbecher — Tue, 19 Apr 2011 13:33:31 GMT

Hi,

maybe this is better:

sum({$<[CUSTOMER_NO] -= {80148}>} DELIVERED) / sum({$<[CUSTOMER_NO] -= {80148}>} DESIRED)

- Ralf

Calculate a Ratio

darrellbutler — Tue, 19 Apr 2011 13:52:03 GMT

Thanks for the reply Ralf, when I do this all service levels default to 100% - wopuld be nice but infortunately not accurate !

Any further ideas, many thanks

Calculate a Ratio

rbecher — Tue, 19 Apr 2011 13:57:14 GMT

I mean, 100% is much more realistic than 400% 😉

Can you post an example app?

- Ralf

Calculate a Ratio

darrellbutler — Tue, 19 Apr 2011 14:06:09 GMT

Very Good Ralf - I'll try that one next time I'm speaking with our accountants !

I've got round this now by splitting my set analysis into two separate expressions then just dividing one into the other using the the column() command. Does the trick.

Thanks for your time

Rgds

Darrell.