topic Re: Change Timezone based on User Selection in QlikView

Change Timezone based on User Selection

Tue, 16 Sep 2014 19:46:12 GMT

I am attempting to change the time stamps to a user specified timezone. I have attached a QVW to better demonstrate what I am attempting to accomplish.

The TimeZones table has the name displayed in the multi-selector box and the timezone name QlikView understands. Then, somehow, I will need the timezone name QlikView understands to use in the ConvertToLocalTime function.

Thank you for your assistance.

Re: Change Timezone based on User Selection

jerem1234 — Tue, 16 Sep 2014 20:33:13 GMT

I used this expression for the variable vTimeZone:

=if(StandardTimeZoneName like GetFieldSelections(StandardTimeZoneName), QlikViewTimeZoneName)

Then used this in the chart:

=ConvertToLocalTime(Timestamp, vTimeZone)

Please find attached.

Hope this helps!

Re: Re: Change Timezone based on User Selection

Wed, 17 Sep 2014 00:00:52 GMT

This helped out a lot. I took your suggestions one step further and applied to the month selector and created a year selector. The selectors work, but when you change the time zone,it get goofy. After changing the time zone, the rows in the ConvertedDate chart no longer reflect the selections accurately. I added a current selection box to see the selections because the selectors no longer show the selections in green. Clearing all selections and reselecting the selections causes the ConvertedDdate chart to accurately display. I have attached a document with the changes that I made.

Do you have any suggestions on how to get around this nuance?

Re: Re: Re: Change Timezone based on User Selection

jerem1234 — Wed, 17 Sep 2014 14:26:39 GMT

You might have to use a date island (table of dates with no links to another table) in this situation, then use an expression to filter the chart. The expression might look something like:

if(Monthname(ConvertToLocalTime(Timestamp, vTimeZone))>= min(MonthYear) and Monthname(ConvertToLocalTime(Timestamp, vTimeZone)) <= max(MonthYear), ConvertToLocalTime(Timestamp, vTimeZone))

To get the date island you can try this code out:

TempMaxMin:

Load

max(Timestamp) as MaxDate,

min(Timestamp) as MinDate

Resident TableA;

Let vMinDate = floor(addmonths(peek('MinDate'), -1));

Let vMaxDate = floor(addmonths(peek('MaxDate'), 1));

MasterCalendarTemp:

LOAD DISTINCT

MonthName($(vMinDate)+rowno()) as MonthYear,

year($(vMinDate)+rowno()) as Year

AutoGenerate(($(vMaxDate)-$(vMinDate)));

LET vMinDate = NULL();

LET vMaxDate = NULL();

Drop Table TempMaxMin;

You'll have to change each chart and add an expression like the above to it, but use the expression you need in the second parameter of the if statement. You could also try using a calculated dimension as well, but you'll need to wrap the function above with aggr (since you can't use aggregation functions in calculated dimensions) like:

aggr(if(Monthname(ConvertToLocalTime(Timestamp, vTimeZone))>= min(MonthYear) and Monthname(ConvertToLocalTime(Timestamp, vTimeZone)) <= max(MonthYear), ConvertToLocalTime(Timestamp, vTimeZone)), Timestamp)

Please find attached. Check out lower table and listboxes.

Hope this helps!

Re: Re: Change Timezone based on User Selection

Thu, 18 Sep 2014 12:36:35 GMT

Thanks for the help. This got complicated real fast. I had been considering using the suggestion at this link Easy Date Range Selection with Qlikview | Min-Maxing for a while. I had held off on this due to the amount of effort required to implement. Making the selectors work with timezone selections requires about the same amount of effort.

It would be nice if QlikView worked better with time zones.