Assistance required for Basic user

system · June 8, 2010, 8:06am

Hi ,

I am a new user to Web Intelligence X1 Version 2 and would like if someone could please

assist me with the following:

What Im trying to achieve is to add some formula in a new column within a report. I have beed successful at producing the report including the formating of the fields with date/time in report

i.e

[Last Resolved Date Time] = 06/06/2010 11:13:58 PM
[Incident Reported Date] = 07/06/2010 12:08:58 AM

What I would like to achieve is to automatically popluate a Fail or Pass
in the new column based on the criteria below:

If ([Last Resolved Date Time] - [Incident Reported Date] > 2hrs
and [Prority] is equal to “Critical”
then cell = Fail
else cell = Pass )

If ([Last Resolved Date] - [Incident Reported Date] > 4hrs
and [Prority] is equal to “High”
then cell = Fail
else cell = Pass )

I am a very basic user and do not have much experience with formulas. I have tried
using the formula toolbar but have not managed to get the syntax or required
conversion correct…

Would great appreciate some assistance if possible

Lou (BOB member since 2010-06-08)

system · June 8, 2010, 8:55am

hi

welcome to BOB!

unfortunately webi doesn’t do time difference. it has a DaysBetween() formula but no time difference. There are various ways around this and the easiest is probably to push the time difference calculation down to the database, this means updating the universe to include an object called say duration that uses the syntax for whatever RDBMS your using to calculate the difference between the two datetime cols.

Once you’ve got duration in your report then the IF statement should be easy enough, see webi user guide for syntax. you may also want to consider using Alerters which highlight rows based on a condition.

See this topic for further ideas on how to do this.

As a tip for future, it is better if your subject describes the problem rather than just a generic ‘i need help’ that way people skimming the new messages will recognise topics then can help with.

cheers

AL

agulland (BOB member since 2004-03-17)

system · June 8, 2010, 10:29am

Hi

Thank you for your prompt reply. Im not that technical but will review the link you mentioned. Not sure I really understand yor comments

"push the time difference calculation down to the database, this means updating the universe to include an object called say duration that uses the syntax for whatever RDBMS your using to calculate the difference between the two datetime cols. "

Nevertheless, I will review the link you provided.

So I think what needs to be done is :

Create a report-level variable that calculates the number of seconds between 2 dates: ( as per link eg)
Format variable ( as per link eg )
Use alerters once correct value is returned in report & highlight rows based on a condition that I set… Say Pass is Green … Fail is red

Hopefully Im on the right track…
Regards

Lou (BOB member since 2010-06-08)

system · June 8, 2010, 12:24pm

do you have contact with the person who created the universe you’re reporting against? They should know how to create a new object in the universe that calculates the required duration.

Alternatively you can create a report formula as described by the mentioned in the link.

Using an Alerter is an alternative to display “Pass” or “Fail” in your table. You can either add a new column to your table which uses an if statement to test the condition and then displays “pass” or “fail”. Or you can use an Alerter which performs same test but highlights the row as either red or green depending on whether condition passed or failed. Either method is ok, just a preference

AL

agulland (BOB member since 2004-03-17)

system · June 10, 2010, 3:00am

Thanks again for your reply… have been trying all the options you provided but Im not having much beginners luck…

As suggested, trying to get an object created in the Universe is not a easy task… I don’t think its at all possible given that its managed by an external company and we have no say in what goes in or out… We are expected to use whats there… Anyway moved on to the next option
Well I’ve tried following the instructions in the link provided but I manage to get a variable created [No of seconds] in the report as a measure… I have attached an excel spreadsheet as a copy of the report with the value returned for that variable…( see attached xls )

( note I had to change ("&" to “And”) ( , to ; ) and returned a -nnnnn

Then I tried to format the variable by creating a new column and paste in the second part of the link as a formula…

( note I had to change ("&" to “And”) ( , to ; ) but still could not get it to work… ( See attached error.doc )

=FormatNumber(Floor([Number of seconds]/86400) ;“0”) And " day(s) " And FormatNumber(Floor(Mod([Number of seconds] ;86400)/3600) ;“00”) And “:” And FormatNumber(Floor(Mod(Mod([Number of seconds] ; 86400) ; 3600)/60) ;“00”) And “:” And FormatNumber(Mod(Mod(Mod([Number of seconds] ;86400) ;3600) ;60) ;“00”)

Am I doing it wrong ?

regards
Lou
Elapsed_time_Incident.xls (14.0 KB)

Lou (BOB member since 2010-06-08)

system · June 10, 2010, 6:24am

My friend, what did you do? You changed “&” with logical AND? That was used for string concat, replace AND with normal + sign.

Prashant Purohit (BOB member since 2009-02-18)

system · June 17, 2010, 10:49am

Hi to those that have assisted me so far. I have spent some time digesting the advice given and following accordingly. The good thing is that as a 1st time user I’m learning quite quickly thxs to this valuable forum. So I will outline what i have done to try to resolve this issue even though its not quite there at this point. Hopefully you will see that I have laid out my info as best possible… Here goes…

Please refer to execl attachment… It will make more sense as the formatting on this forum update may confuse a little…

No Of seconds ( Report Variable )

=DaysBetween([Incident Last Resolved Date Time];[Incident Reported Date Time]) * 86400 + ( ToNumber(FormatDate([Incident Reported Date];“HH”)) * 3600 + ToNumber(Left(FormatDate([Incident Reported Date Time]; “mm:ss”) ;2)) * 60 + ToNumber( FormatDate([Incident Reported Date] ;“ss”))) - (ToNumber(FormatDate([Incident Last Resolved Date Time] ;“HH”)) * 3600 + ToNumber(Left(FormatDate([Incident Last Resolved Date Time] ;“mm:ss”) ;2)) * 60 + ToNumber(FormatDate([Incident Last Resolved Date Time] ;“ss”)))

Elapsed time in hh:mm:ss ( Formatted Column )

=FormatNumber(Floor([Number of seconds]/86400) ;“0”) + " day(s) " + FormatNumber(Floor(Mod([Number of seconds] ;86400)/3600) ;“00”) + “:” + FormatNumber(Floor(Mod(Mod([Number of seconds] ;86400) ;3600)/60) ;“00”) + “:” + FormatNumber(Mod(Mod(Mod([Number of seconds] ;86400) ;3600) ;60) ;“00”)

Contents of Query ( Without formatting report columns )

	Incident Reported Date		Incident Last Resolved - Date 
		( mm/dd/yyyy )			( mm/dd/yyyy )

INC000002493598 06/16/2010 06/16/2010
INC000002495791 06/16/2010 06/16/2010
INC000002492887 06.15/2010 06/16/2010

	Incident Reported Date Time	Incident Last Resolved Date Time 
		( dd/mm/yy )			( dd/mm/yy )

INC000002493598 16/06/10 16/06/10
INC000002495791 16/06/10 16/06/10
INC000002492887 15/06/10 16/06/10

Report Variable ( No of seconds ) Report Variable formatted

                      Number of seconds                Elapsed Time

INC000002493598 -25732 -1 day(s) -08:-09:-52
INC000002495791 -48850 -1 day(s) -14:-35:-10
INC000002492887 -83785 -1 day(s) -24:-17:-25

Formats after Report is saved to excel

Incident Reported Date mm/dd/yyyy
Incident Reported Date Time dd/mm/yyyy ( Displays cell as dd/mm/yyyy hh:mm:ss AM )

Incident Last Resolved - Date dd/mm/yyyy ( Displays cell as dd/mm/yyyy 10:00:00 AM
Incident Last Resolved Date Time dd/mm/yyyy ( Displays cell as dd/mm/yyyy hh:mm:ss PM )

Elapsed time calculation in Excel

Incident No: Incident Last Resolved Date Time Incident Reported Date Time Elapsed Time
INC000002493598 16/06/2010 8:04:52 AM 16/06/2010 7:56:41 AM 0:08:11
INC000002495791 16/06/2010 1:59:10 PM 16/06/2010 1:25:23 PM 0:33:47
INC000002492887 16/06/2010 12:06:25 AM 15/06/2010 11:50:51 PM 0.15.34

So I would appreciate if someone could help me put this to bed… Maybe my
formula is wrong or that I still need to add extra smarts to get a result in hh::mm::ss
so that I can apply a filter on that value to determine if its less than or greater than
a time value… eg such as <= 4 hrs…

I appreciate your time ( believe me , im trying as well )… Cheers

Simple_Test_for_Date_Time_difference.xls (13.0 KB)

Lou (BOB member since 2010-06-08)