Forum Discussion

dilkushpatel's avatar
dilkushpatel
Helper III
9 years ago

Date modification by filtered timezone

scenario:

I have a filter using which user can select country

 

Now once country is selected I want to modify all dates to timezone of that country

 

I have created measures which works fine as they work within context of selection, however this measures will not help as I want to show charts where i show rowcount within hour band which is based on date in appropriate timezone.

 

Calculated columns nor working as it considers whole timezone table while calculating and will not consider selected timezone

 

Any idea how can i achieve this?

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    So when you say you want to modify dates to that timezone of the country do you mean that you want to have the Date/Time shift up or down based on where they are on the GMT?

     

    My solution would be to firstly work out what Timezone your dates are recorded as.  For this example i'll presume that they represent a date/time in Greenwich (so GMT = 0).

    Now create a Timezone table with 2 column, the Name of the timezone for your dropdown and the GMT modifier.  Here in Brisbane Australia, our modifier is GMT + 10.  Time data is stored as a decimal number between 0 and 1, so for Brisbane i'd need to store (10 / 24) as my modifier.  If you have a place that was GMT - 10, you'd need to store (-10 / 24).  This column we'll call [Timezone] and set as a number.

    Now whenever you wish to display a date/time, your measure just needs to show [DateField] + MIN([Timezone]). Since your user will have selected a timezone, this will always return that selected value.  (If non is selected, you'll get the Lowest GMT value).

    • dilkushpatel's avatar
      dilkushpatel
      Helper III
      9 years ago

      Anonymous

      I agree with you till the point of measure will work as long as we show each date on report

      how ever when aggregates are required it will fail

       

      Consider scenario,

      I have calculated TimeBand depending on during which hour call was started

      Now I have to show number of calls by this hour Band then it will need band as dimension and count of either dates or id column as measure

       

      Now with this report if I use Modified call dt which is measure then t will not work as each date is not selected.