topic Need help with FABS in QlikView

Need help with FABS

Wed, 06 Mar 2013 15:02:37 GMT

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.

Re: Need help with FABS

Anonymous — Wed, 06 Mar 2013 21:41:17 GMT

Paul,

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

Regards,

Michael

Re: Need help with FABS

Thu, 07 Mar 2013 15:45:00 GMT

Not sure how to use this function, please elaborate.

Paul.

Re: Need help with FABS

Anonymous — Thu, 07 Mar 2013 18:13:34 GMT

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

Re: Need help with FABS

Fri, 08 Mar 2013 10:31:02 GMT

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.