You need to have an aggregation function in your If syntax. Try:
Sum( f(ConsumptionFrom<=Consumption and Consumption<=ConsumptionTo, BasicRate+EnergyRate*Consumption,-999) )
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?
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.
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?
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:
(if(ConsumptionFrom<=Consumption and Consumption<=ConsumptionTo,EnergyRate,0)
if(ConsumptionFrom<=Consumption and Consumption<=ConsumptionTo,BasicRate,0)
I hope that the parenthesis are placed correctly. Consumption * EnergyRate + BasicRate