Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello, I am working on a report to do with meters which have 4 dials, this means when they roll from 9999 they go back to zero, this causes complications when calculating consumption.
First off I used max(if(Read_Year='08/09',Reading))-min(if(Read_Year='08/09',Reading)) and spotted that certain accounts that had very high consumption (due to the meter rolling over) so I then used :- Fabs(max(if(Read_Year='08/09',Reading))-min(if(Read_Year='08/09',Reading))-9999) to remedy the problem.
This fixes the issues with the meters who roll over from 9999 to 0000+ but messes up the results with ones that arent affected. any ideas on what I can do to fix this?
Paul.
Paul,
Take a look at mod() function. Maybe it better than fabs() in your case.
Regards,
Michael
Not sure how to use this function, please elaborate.
Paul.
Paul,
Mod() function returns reminder of an integer division. Examples, all with the base 10000:
mod(0,10000) returns 0
mod(10000,10000) returns 0
mod(1,10000) returns 1
mod(5,10000) returns 5
mod(10005,10000) returns 5
mod(250000,10000) returns 0
mod(250005,10000) returns 5
I think it is the best for counters.
Regards,
Michael
Another option:
If you can find out that there has been a roll-over, you can just add 10000 to the smallest value, and then subtract the values. For example:
Suppose the first meter reading is 9990 and the second reading is 10, you can do the following subtraction:
(10+10000) - 9990.
This will yield the expected result.