interesting average

system · November 19, 2008, 12:58pm

Hello,

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”.

Any idea how to solve this?

biankutzu22 (BOB member since 2008-05-16)

system · November 19, 2008, 1:23pm

I tried but I got stuck what this means?

I think Difference between two dates couldn’t be negative…

Also if this could help you…!!!

aniketp (BOB member since 2007-10-05)

system · November 19, 2008, 1:38pm

Hy,

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.

biankutzu22 (BOB member since 2008-05-16)

system · November 19, 2008, 1:38pm

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.

debbie

Debbie (BOB member since 2005-03-01)

system · November 19, 2008, 1:53pm

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”

biankutzu22 (BOB member since 2008-05-16)

system · November 19, 2008, 4:20pm

So don’t put N/A. Make sure you only have numbers in the column of which you are calculating the average.

Debbie

Debbie (BOB member since 2005-03-01)

system · November 20, 2008, 6:56am

I have to put “N/A”. But I’ve solved the problem and my report looks just fine

biankutzu22 (BOB member since 2008-05-16)

system · November 20, 2008, 8:15am

Could you please post your solution for others

aniketp (BOB member since 2007-10-05)

system · November 20, 2008, 8:26am

Sure,

Initially for “number of seconds” I used this formula (founded on this forum: )):

[number of seconds] = DaysBetween([DATA 1];[DATA 2]) * 86400 + ( ToNumber(FormatDate( [DATA 2];“HH”)) * 3600 + ToNumber(Left(FormatDate( [DATA 2];“mm:ss”);2)) * 60 + ToNumber(FormatDate( [DATA 2];“ss”))) - (ToNumber(FormatDate([DATA 1] ;“HH”)) * 3600 + ToNumber(Left(FormatDate([DATA 1];“mm:ss”);2)) * 60 + ToNumber(FormatDate([DATA 1];“ss”)))

I’ve changed the formula to give me a negative response in case that one of the dates is NULL. So the new “numbers of seconds” looks like this:

[number of seconds] = =If(IsNull([DATA 1]) Or IsNull([DATA 2]);ToNumber("-9999");DaysBetween([DATA 1];[DATA 2]) * 86400 + ( ToNumber(FormatDate( [DATA 2];“HH”)) * 3600 + ToNumber(Left(FormatDate( [DATA 2];“mm:ss”);2)) * 60 + ToNumber(FormatDate( [DATA 2];“ss”))) - (ToNumber(FormatDate([DATA 1] ;“HH”)) * 3600 + ToNumber(Left(FormatDate([DATA 1];“mm:ss”);2)) * 60 + ToNumber(FormatDate([DATA 1];“ss”)))).

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.

biankutzu22 (BOB member since 2008-05-16)

system · November 20, 2008, 12:53pm

biankutzu22,

Every solution is interesting, could you please send a doc. containing Problem defination and Solution with screenshot(if possible)

Otherwise I understood the problem and solution, but the screen shots will definitely more helpful.

And thanks for your response.

aniketp (BOB member since 2007-10-05)

system · November 20, 2008, 1:02pm

I’ve read the whole topic once again and I think that everything I can say it’s said.

I’m sorry, but I can’t give any screen shots (real data).

biankutzu22 (BOB member since 2008-05-16)

system · November 20, 2008, 1:10pm

I do understand, but it’s possible to give without data also,

but this problem won’t work without data.

So I too think it’s not good idea.

Anyways thanks for your response.

aniketp (BOB member since 2007-10-05)