topic Re: Calculate KPI in QlikView

Calculate KPI

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

hi guys,

I have a little problem with KPI calculation.

I've this situation:

Date	employees	revenue
01/01/2017	5	120000
01/02/2017	5	125400
01/03/2017	5	150000
01/04/2017	5	140000
01/05/2017	5	132500

I have to calculate revenue / employees like :

sum(revenue) / employees

that is:

667900 / 5

But on qlikview if I write:

= sum(revenue) / employees

I get back " - " symbol

so I try:

= sum(revenue) / sum(employees)

this works but the result is incorrect because the values are:

667900 / 25

How can I do?

This is an example and the original table have more values than this.

Thanks

Re: Calculate KPI

Anil_Babu_Samineni — Thu, 07 Dec 2017 16:51:18 GMT

May be this?

sum(revenue) / Count(employees)

Re: Calculate KPI

Anonymous — Thu, 07 Dec 2017 16:58:23 GMT

I tried, but this solution doesn't work

Re: Calculate KPI

trdandamudi — Thu, 07 Dec 2017 17:03:34 GMT

Try as below:

Sum(revenue) / Count(Total employees)

Re: Calculate KPI

Anil_Babu_Samineni — Thu, 07 Dec 2017 17:04:29 GMT

Why not?

Re: Calculate KPI

Clever_Anjos — Thu, 07 Dec 2017 17:05:46 GMT

sum(revenue) / sum(employees)

Re: Calculate KPI

Anonymous — Thu, 07 Dec 2017 17:08:34 GMT

Doesn't work

Re: Calculate KPI

Anonymous — Thu, 07 Dec 2017 17:10:42 GMT

This example works because there are 5 months, but if you add june, the result of count(employees) will be 6, but the real number of employees is 5

Re: Calculate KPI

Anil_Babu_Samineni — Thu, 07 Dec 2017 17:13:16 GMT

What you mean real ?? May be this? If none of them not works, I would suggest provide real data

sum(revenue) / Count({<employees = {5}>}employees)

Re: Calculate KPI

Anonymous — Thu, 07 Dec 2017 17:13:57 GMT

In this case the result of sum(employees) is 25, but the real number of employees is 5, so this solution doesn't work.

I need to divide the sum(revenue) for the number of employees

Re: Calculate KPI

Anonymous — Thu, 07 Dec 2017 17:21:09 GMT

every moth the system receves a file like this

id	date	employees	revenue	n.call	etc....

and there are many lines.

The number of employees is a constants, but I have very different values, because I have many different id.

I need to calculate sum(revenue) / employees

where the number of employees isn't a sum(employees), but only a single value.

Do you understand now my problem?

Re: Calculate KPI

Anil_Babu_Samineni — Thu, 07 Dec 2017 17:25:56 GMT

Still not sure, Even thought what you tried is

sum(revenue) / 5

So, If you need to constant even selection happened

sum(revenue) / Count({1}employees)

Re: Calculate KPI

Anonymous — Thu, 07 Dec 2017 18:07:20 GMT

Hi, Try it, please

sum(revenue) / sum(Distinct employees)

Regards.

Re: Calculate KPI

MarcoWedel — Thu, 07 Dec 2017 21:37:35 GMT

Hi,

your expression doesn't show this behaviour with your sample data:

Instead I guess that you have changing employee numbers in your real data that causes the implicit only() aggregation of your expression to return null.