Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

seebach
Contributor III

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

Re: Calculate of a new KPI doesn't work

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).

seebach
Contributor III

Re: Calculate of a new KPI doesn't work

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

Re: Calculate of a new KPI doesn't work

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.

seebach
Contributor III

Re: Calculate of a new KPI doesn't work

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?

seebach
Contributor III

Re: Calculate of a new KPI doesn't work

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

Re: Calculate of a new KPI doesn't work

you are right. thank you very much.

it works now so wonderful :-)

seebach
Contributor III

Re: Calculate of a new KPI doesn't work

You're welcome. Viel Glück