I’m missing something in calculating an average. In my report I have
ORDER_ID STATUS_DATE1 STATUS_DATE2
123 17.11.2008 18.11.2008
My job is to calculate the difference between STATUS_DATE2 and STATUS_DATE1. DATE2 must be grater than DATE1
There are several situations:
both STATUS_DATE have values and the difference is positive. So I display the difference
both STATUS_DATE have values and the difference is negative. So I display “INVALID”
3.DATE1 or DATE2 don’t have values so the message is “N/A”.
Also, I have to calculate an average for the column with the difference(DATE2-DAT1). If the only values showed are “INVALID” and the difference the average is calculated (the condition is average(dif) where dif>0). If “N/A” is between the values the average returns “#ERROR”.
the difference can be negative. Normally the date for STATUS2 should be grater than the date for STATUS1, but the users can insert wrong values(dates) so I have to treat that case also.
So, I calculate the number of seconds between the 2 dates and this number sometimes can be negative. This case it’s OK for me because I can calculate the average. The problem appears when one of the 2 dates is NULL.
You can’t calculate the average of something that isn’t a number! So if your difference can include INVALID or N/A, then you can’t calculate an average of that column.
Either replace INVALID and N/A with a numeric value (eg 0), so you can calculate an average of valid differences, or have a second column which contains a variable that says:
if difference is valid, put difference, else put 0
Then calculate the average of this second column. That way you get to keep your INVALID and N/A, but still get an average.
I can calculate the average when I have “INVALID” because in this case “number of seconds” is negative. I calculate the average only for the positive numbers.
The problem is with “N/A”.
I do not display in my column “number of seconds”, but something that looks like “0 day(s) 12:12:12”. So “number of seconds” will still be"#ERROR" in case that one of the 2 dates is NULL.
Example:
in the column “DATE2 - DATE1” I have:
=If(IsNull([DATE1]) Or IsNull([DATE2]);“N/A”;If([invalid data ]<0;“INVALID”;[difference]))
where [invalid data] =ToNumber((FormatNumber(Floor([number of seconds]/86400);“0”))) - I check if the difference is negative
and [difference]=Concatenation(ToNumber((FormatNumber(Floor([number of seconds]/86400);“0”)));Concatenation(" day(s) “;Concatenation(ToNumber(FormatNumber(Floor(Mod([number of seconds];86400)/3600);“00”));Concatenation(” : " ;Concatenation(FormatNumber(Floor(Mod(Mod([number of seconds ];86400);3600)/60);“00”);Concatenation(" : ";FormatNumber(Mod(Mod(Mod([number of seconds ];86400);3600);60);“00”))))))) - this is the display of “number of seconds”
In the report I’ve created 2 alerters for the 2 dates: if any of the 2 dates is NULL then in the column will appear the text “N/A”, but the real value will be “-9999”. And I calculate the average only for the positive values.
I can give the entire solution(every step) if anyone is interested.