1 Reply Latest reply on Oct 31, 2013 11:32 AM by Tracy Rodgers

    Change null values to blanks for datetime

    Michael Carper

      Hi,

       

      I've got a data set with events as dimensions, and certain event types are missing several fields. When a field is missing, I still want the event type to show, but with the missing fields as blank. This isn't a problem to configure for string fields, as I just use a

      IF [Event] = EventType1, '',...ELSE [String]

      kind of formula. However, I'm having trouble producing the same results for the datetime fields. I can't use the IF or ISNULL statement with '', since that's mixing date types. I'm not given an option in formatting to handle nulls a certain way. Does anyone know a way to show null datetimes as blank? The attached workbook has my exact data.

        • 1. Re: Change null values to blanks for datetime
          Tracy Rodgers

          Hi Michael,

           

          You can do this by creating two calculated fields similar to the following. For Second:

           

          if isnull([Time]) then "00:00:00" else "00:" + (if datepart('minute', Time)<=9 then "0" +datename('minute', Time) else datename('minute', Time) end)+ ":" +

          ( if datepart('second', Time)<=9 then "0" +datename('second', Time) else datename('second', Time) end) end

           

          For Minute:

           

          if isnull([Pace]) then "00:00:00" else "00:" + (if datepart('minute', Pace)<=9 then "0" +datename('minute', Pace) else datename('minute', Pace) end)+ ":00"  end

           

          Hope this helps!

           

          -Tracy