Need help with FABS

[#1]

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.

[Anonymous]

Paul,

Take a look at mod() function.  Maybe it better than fabs() in your case.

Regards,

Michael

[#3]

Not sure how to use this function, please elaborate.

Paul.

[Anonymous]

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

[#5]

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.