topic Re: Date/Time formatting in If statement in App Development

Date/Time formatting in If statement

Ruthie09 — Thu, 22 Aug 2019 20:44:50 GMT

Please help. I can't seem to get my table dates to appear in the proper format when using an "if" statment

I have table called TimeZones (which lists all the Qlik-compatible time zone locations).
I have a variable called SelectedTimeZone that uses the GetFieldSelections function to return the value selected by the user in a single-select filter called TimeZoneRegions.
I have a table of help desk tickets that, for testing purposes, contains the created date & time for each ticket (SYS_CREATED_ON) and a field called Modified Created that should show the SYS_CREATED_ON converted to the user-selected UTC offset time.

For the most part, the conversion works correctly. When I set Modified Created formula to be only =ConvertToLocalTime(SYS_CREATED_ON, '$(SelectedTimeZone)'), the Modified Created column in the table is formatted correctly (and displays the applicable value based on the location selected)

However, when I set the Modified Created field to display the SYS_CREATED_ON value if nothing is selected in the TimeZoneRegions filter (see formula below), the column displays in numeric decimal format vs standard date/time format (regardless of whether or not a value is selected).

=if(isnull(TimeZoneRegions), SYS_CREATED_ON, ConvertToLocalTime(SYS_CREATED_ON, '$(SelectedTimeZone)'))

How do I get it to display correctly?

Re: Date/Time formatting in If statement

gmenoutis — Thu, 22 Aug 2019 21:05:17 GMT

Just use date(SYS_CREATED_ON).

The function ConvertToLocalTime expects a timestamp as first argument, and is therefore able to read the (truly numeric) value as a date. Then it returns a dual value, which actually has the numeric value in the numeric part, but it also has in the text part the human-friendly version.

On the contrary, when you just read SYS_CREATED_ON, you are reading a numeric value. You have to use date() in order to make the engine use the aformentioned dual functionality.

Re: Date/Time formatting in If statement

Ruthie09 — Thu, 22 Aug 2019 21:39:56 GMT

I did modify your solution a bit by using timestamp() vs date() because I need the time to be displayed. Thank you for helping me.

Also, I changed it so the first value in the list of TimeZoneRegions was "- none -" and set that to force the default value to be equiv to SYS_CREATED_ON (since it kept trying to change the time by 2 hours even when no value was selected).

=if(TimeZoneRegions='- none -', timestamp(ConvertToLocalTime(SYS_CREATED_ON,'GMT')), timestamp(ConvertToLocalTime(SYS_CREATED_ON, '$(SelectedTimeZone)')))