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

    Change null values to blanks for datetime

    Michael Carper



      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!