13 Replies Latest reply: Dec 7, 2017 4:37 PM by Marco Wedel

# Calculate KPI

hi guys,

I have a little problem with KPI calculation.

I've this situation:

Dateemployeesrevenue

01/01/2017

5

120000
01/02/20175125400
01/03/20175150000
01/04/20175140000
01/05/20175132500

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

May be this?

sum(revenue) / Count(employees)

• ###### Re: Calculate KPI

I tried, but this solution doesn't work

• ###### Re: Calculate KPI

Why not?

• ###### Re: Calculate KPI

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

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

every moth the system receves a file like this

iddateemployeesrevenuen.calletc....

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

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

Try as below:

Sum(revenue) / Count(Total employees)

• ###### Re: Calculate KPI

sum(revenue) / sum(employees)

• ###### Re: Calculate KPI

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

Hi, Try it, please

sum(revenue) / sum(Distinct employees)

Regards.

• ###### Re: Calculate KPI

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.

The Only Function

It’s all Aggregations

Use Aggregation Functions!

One solution to calculate this cumulated revenue per employee might be simply:

```=Sum(revenue / employees)
```

hope this helps

regards

Marco