Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
tseebach
Luminary Alumni
Luminary Alumni

Re: Calculate of a new KPI doesn't work

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

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

thanks for the answer.

the result of your suggestion looks like this:

pic2.PNG

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

tseebach
Luminary Alumni
Luminary Alumni
Author

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?

Not applicable

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.

tseebach
Luminary Alumni
Luminary Alumni
Author

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:

Untitled copy.png

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

tseebach
Luminary Alumni
Luminary Alumni
Author

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

Not applicable

you are right. thank you very much.

it works now so wonderful 🙂

tseebach
Luminary Alumni
Luminary Alumni
Author

You're welcome. Viel Glück