0

First time posting. Our fire response agency just moved to Cognos 11 after qRep. I know little to nothing about SQL, modules, cubes, or data analytics, but I'm one of the few in our agency that can write an excel function so I guess I am considered the in-house data expert.

With that in mind, I need some help. I want to compare a calculated result (time interval) to a static goal. For instance, if the calculated result is 01:00, how do I express a static goal of 01:20 as a time interval to make the calculation work?

Here is the formula I used for my "Static Goal" column:

IF([Inc Cat]='EMS') then ('60')) else
IF([Inc Cat]='FIRE') then ('80')) else
NULL

I need to convert the 60 and 80 above as seconds so I can compare a calculated time interval to these values.

I appreciate any help, but please review the second sentence of this post prior to replying.

Oliver.R
  • 1,282
  • 7
  • 17
Ryan51104
  • 11
  • 1

1 Answers1

1

Solved. I removed the single quotes and wrote out the complete time.

IF([Inc Cat]='EMS') then (0 00:01:10.000) else IF([Inc Cat]='FIRE') then (0 00:01:20.000) else NULL

Ryan51104
  • 11
  • 1
  • A case statement would be aesthetically cleaner. You've just discovered the concept of data types. I have the sinking feeling you're doing this entirely in a report without much in your model. Please don't do that. If you want the value for EMS to be 60 seconds why are you making it 1 minute 10 seconds? – C'est Moi Mar 29 '20 at 22:56