App Development

[Ruthie09]

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?

[gmenoutis · Accepted solution]

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.

[Ruthie09]

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)')))