4 Replies Latest reply: Sep 18, 2014 12:06 PM by Robert Eder RSS

    Change Timezone based on User Selection

      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
          Jeremiah Kurpat

          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

              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
                  Jeremiah Kurpat

                  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!