Linking field value with different fields

[raju_salmon]

Hi,

I have a fact table with different timezones, and attached sample application.

The requirement is, i need to provide the list of timezones in a multibox, so i will put always one selected value.

Based on time zone selection, i need to show the all KPI numbers.

I will provide Date, Week, Month, Year field on dashboard... this calendar will be linked to PST. However we need to list all time zones and user first select his time zone and see the KPI values.

Is there any possible way to do this?

Any other way to filter the data, based on time zones. I do not want to concatenate FACT table 4 time to link with date field.

Thanks for your time.

[Anonymous]

Why don't you want to concatenate FACT table 4 time to link with date field ?

Seems like a good way to sort it.

[raju_salmon]

Thanks for reply bill. We have more than 15 timezones and fact is too large. So cannot able to think about it.

[Anonymous]

How many fact rows ?

[raju_salmon]

Right now, 11-12 millions available... but will keep on increasing day by day 100,000+

[Anonymous]

I presume then that your real data source is a database and not spreadsheet.  Plus you shall be doing an incremental load.

You could look at the sql being used for the data extract and the rdbms data model, to see if the data extract could be sorted better there.

Or you could think about using the ConvertToLocalTime() function in the front end of maybe the script.

[raju_salmon]

Yes Bill, data available in database. And doing incremental load as you guess.

Since the table structure is finalized with different time zones, we need to work from QV side to provide filters to user in the better way.

I though providing multibox with list of time zones and once user finalize it, then he can go for remaining filter.

So, i am thinking how to do it. Can we achieve this with ConvertToLocalTime()?

Any friendly way to show all time zone to end users? The mail question is how to all link these with main dates which will show week, month, querter, year etc.

[Anonymous]

Maybe create a Calendar of all the different time zone date fields concatenated together with a [Time Zone] field.  Thus you main fact table will only have to hold the UTC date values.

Then use Set Analysis in your expressions to use the [Time Zone] selected.  With the List Box for [Time Zone] having Always One Selected Value ticked.

This blog by Henric could give you inspiration :

    Canonical Date