if todate is >previous date

[hlb6]

I need to do the following:
In this example, the data is bad but we need to account for it as best we can.

The ofe_hourmeter1 is like the odometer in your car. There is no way that your odometer reading on 2/25 can be greater than your odometer reading on 2/28.

We are doing calculations on beginning hour meter read and ending hour meter read so the calculation in the report is 5317 – 5319 = -2.

So, I think the answer is to show the result of the calculation only if it is a positive number. If it is a negative number, then the difference will be 0.

This is the formula I have so far:

IF COUNT ({work_order_test;1.Meter}, {work_order_test;1.EquipNumber})> 1
THEN MAXIMUM ({work_order_test;1.Meter}, {work_order_test;1.EquipNumber})
- MINIMUM ({work_order_test;1.Meter}, {work_order_test;1.EquipNumber})
ELSE
IF COUNT ({work_order_test;1.Meter}, {work_order_test;1.EquipNumber}) = 1
THEN {work_order_test;1.Meter}
ELSE
SUM ({work_order_test;1.Meter}, {work_order_test;1.EquipNumber})

Now I know I have to put another grouping with the invoice date and a if statement in there to get the max date ( if ?p_Periodto (my invoice end date) > ?p-Periodfrom (my invoice begindate))>0 then do the above with an invoice grouping. I am having a hard time getting the grouping and the date part to work. The meter needs to be in the invoice grouping also; it is in another group equipment. I also have to account for duplicate dates - the I have to take the first of the dup date if the meter is the same and then do my formula above with in the invoice grouping.

INV DATE INVOICE# METER

1/31/2013 SH300014981 2287
1/31/2013 SH300014997 2287
1/31/2013 SH300014998 1919
3/14/2013 SH300015449 2420

I need to get the difference of the meter where the bottom date (3/14/2013) is greater than the previous date (1/31/2013), and grabing the max meter of the 1/31/2013 date - do the calculation if the result is > 0 the the result, if it is <= 0 then the meter is to be 0.

I hope I explained this well enough - it's confusing to me also! I need this soon - I have been working on it all week with no luck.

 

[pmax9999]

I have re-read your post numerous times and am still not sure I understand. I am assuming that the Inv Date/Invoice #/Meter sample data you posted is for a single Meter, but not sure what the answer is that you are looking for. From what I think I understand, I would guess the answer 133, being the difference between 2420 (the latest Meter Reading [based on Invoice Dates]) and 2287 (the maximum value from the previous Meter Reading [again, based on Invoice Dates]. Is that correct?

Before I contemplate developing a solution, can you advise:
1. Would there only ever be 2 distinct dates?
2. Based on your example, is it possible there could be 2 invoices on 3/14/2013 and if so, how would you determine which Meter Reading to use (ie, maximum, highest Invoice Number etc)

It would help if you could provide a couple more examples of realistic data and the result you need.

Cheers
Pete

 

[pmax9999]

OK, it seems I need to get a life (or at least another interest for these cold and miserable Aussie winter days).

On the assumption that my interpretation is correct, try the following:

Create the following formula and place it in the GH Section of the Meter group:

WhilePrintingRecords;
Global NumberVar READ_From := 0;
Global NumberVar READ_To:= 0;

Create the following formula and place it in the Details Section (amending field names as appropriate):

WhilePrintingRecords;
Global NumberVar READ_From;
Global NumberVar READ_To;

If      {Table.INV_DATE} = Minimum({Table.INV_DATE},{Table.DEVICE})
Then    If      {Table.METER_READING} > READ_To
        Then    READ_To := {Table.METER_READING}
        Else    READ_To := READ_To
Else    READ_To := READ_To;

If      {Table.INV_DATE} = Maximum({Table.INV_DATE},{Table.DEVICE})
Then    If      {Table.METER_READING} > READ_From
        Then    READ_From := {Table.METER_READING}
        Else    READ_From := READ_From
Else    READ_From := READ_From;

Create the following formula to calculate the difference between the 2 Meter readings and place it in the GF Section of the Meter group:

WhilePrintingRecords;
Global NumberVar READ_From;
Global NumberVar READ_To;

READ_From - READ_To

The result is the dfference between the Highest reading on the earliest Invoice date and the highest reading on the latest Invoice date.

There are almost certainly floors in my assumptions - if so and you need further assistance, post back with the answer to the questions from my earlier post, and explain where my assumptions are wrong and what the result should be.


Cheers
Pete

 

[hlb6]

Thanks pmax9999 first for the help.

I created the first two parts of your code:

whileprintingrecords;
global numbervar read_from :=0;
global numbervar read_to := 0;
PLACED IN THE GROUP HEADER

whileprintingrecords;
global numbervar read_from;
global numbervar read_to;

if {work_order_test;1.InvoiceDate}=minimum({work_order_test;1.InvoiceDate},{work_order_test;1.Model})
then
if {work_order_test;1.Meter} > read_to
then read_to := {work_order_test;1.Meter}
else read_to := read_to
else read_to := read_to;

if {work_order_test;1.InvoiceDate} = maximum({work_order_test;1.InvoiceDate},{work_order_test;1.Model})
then
if {work_order_test;1.Meter} > read_from
then read_from := {work_order_test;1.Meter}
else read_from := read_from
else read_from := read_from;

When I check the formula, it tells me that ({work_order_test;1.InvoiceDate},{work_order_test;1.Model}) there must be a group that matches this field - what am I now doing wrong??????

 

[hlb6]

I played around with the second formuls and finally got no errors ; here is my code

whileprintingrecords;
global numbervar read_from;
global numbervar read_to;

if {work_order_test;1.InvoiceDate}=minimum({work_order_test;1.InvoiceDate}, {work_order_test;1.EquipNumber})
then
if {work_order_test;1.Meter} > read_to
then read_to := {work_order_test;1.Meter}
else read_to := read_to
else read_to := read_to;

if {work_order_test;1.InvoiceDate} = maximum({work_order_test;1.InvoiceDate},{work_order_test;1.EquipNumber})
then
if {work_order_test;1.Meter} > read_from
then read_from := {work_order_test;1.Meter}
else read_from := read_from
else read_from := read_from;

I will now place taht in the details section of my report.

 

[hlb6]

Okay - just ran the reopt and it is giving me 0 where there should be a meter reading.

 

[hlb6]

This is what my result should be for the meter reading:

Inv Date Meter
5/15/2013 2287
5/15/2013 2559

the meter result should be 272 = 2559-2287 not 0 which is what I am getting

then I have to make sure that on the next date that is subtracts the max meter of 5/15/2013 fom the max meter of lets say, 5/30/2013.

I'll play but additional help in this sand box would be helpful

 

[hlb6]

I have my little headache figured out - here is my code:

IF COUNT ({work_order_test;1.Meter}, {work_order_test;1.EquipNumber})> 1
THEN
(IF (MAXIMUM({work_order_test;1.Meter},{work_order_test;1.EquipNumber}) -
MINIMUM({work_order_test;1.Meter},{work_order_test;1.EquipNumber}))> 0
THEN(MAXIMUM({work_order_test;1.Meter},{work_order_test;1.EquipNumber}) -
MINIMUM({work_order_test;1.Meter},{work_order_test;1.EquipNumber}))
ELSE 0.00)
ELSE

IF COUNT ({work_order_test;1.Meter}, {work_order_test;1.EquipNumber})= 1
THEN
(IF (MAXIMUM({work_order_test;1.Meter},{work_order_test;1.EquipNumber}) -
MINIMUM({work_order_test;1.Meter},{work_order_test;1.EquipNumber}))> 0
THEN(MAXIMUM({work_order_test;1.Meter},{work_order_test;1.EquipNumber}) -
MINIMUM({work_order_test;1.Meter},{work_order_test;1.EquipNumber}))
ELSE 0.00)
ELSE
MAXIMUM({work_order_test;1.Meter},{work_order_test;1.EquipNumber}) -
MINIMUM({work_order_test;1.Meter},{work_order_test;1.EquipNumber})


Thanks for your help!