topic Re: Calculate of a new KPI doesn't work in App Development

Re: Calculate of a new KPI doesn't work

tseebach — Mon, 16 Mar 2015 12:21:45 GMT

You need to have an aggregation function in your If syntax. Try:

Sum( f(ConsumptionFrom<=Consumption and Consumption<=ConsumptionTo, BasicRate+EnergyRate*Consumption,-999) )

Re: Calculate of a new KPI doesn't work

Mon, 16 Mar 2015 12:38:55 GMT

thanks for the answer.

the result of your suggestion looks like this:

but this cannot be the right solution (compare the last 3 values).

Re: Calculate of a new KPI doesn't work

tseebach — Mon, 16 Mar 2015 12:44:29 GMT

Well, now you are getting results. The Sum function might however not be the right solution.

If a customer has more than 1 set of values, how should these be handled?

And what do you mean by compare last 3 values?

Re: Calculate of a new KPI doesn't work

Mon, 16 Mar 2015 12:52:29 GMT

there ist only one row in then table for every customer. so the solution must be unique.

in the graphic you can see negative results for the ID 8,5 and 16. a look in the table

together with the formular BasicRate+EnergyRate*Consumption you can see that this

is not impossible.

Re: Calculate of a new KPI doesn't work

tseebach — Mon, 16 Mar 2015 13:28:44 GMT

No, thats not right. There is more than one row for most customers. Try making a Table and put all fields into it, then select 1 customer.

You can see that there are multiple rates for each customer. Once the 2 tables are linked. You need to handles these rows, and select the values you wish to calculate on.

This is my result for customer 4:

I'm assuming that the key is the field Rate?

Re: Calculate of a new KPI doesn't work

tseebach — Mon, 16 Mar 2015 13:38:16 GMT

But I think I get want you want to do. You like to extract the Rate(s) for a certain consumption. I've would do it using the IntervalMatch in the script. But you can also do it using this expression:

sum(

(if(ConsumptionFrom<=Consumption and Consumption<=ConsumptionTo,EnergyRate,0)

*Consumption)+

if(ConsumptionFrom<=Consumption and Consumption<=ConsumptionTo,BasicRate,0)

)

I hope that the parenthesis are placed correctly. Consumption * EnergyRate + BasicRate

Re: Calculate of a new KPI doesn't work

Mon, 16 Mar 2015 15:37:40 GMT

you are right. thank you very much.

it works now so wonderful 🙂

Re: Calculate of a new KPI doesn't work

tseebach — Mon, 16 Mar 2015 15:41:55 GMT

You're welcome. Viel Glück