1 fact table, 2 date dimensions, 1 master calendar

[amien]

the situation:

1. a fact table that contains two date dimensions (first created date and departure date)

2. 1 master calendar

3. a linked table.

the master calendar can only link to 1 date dimension in the fact table. how can i link the master calender to both date dimensions? the only solution that i see is to read the fact tables twice and create two fact tables. is that the best solution?

[#2]

Hi Amien,

whats the purpose of the master calendar? in other words, what should happen if i select may 10th 2011?

Regards,

Reyman

[amien]

i have several fact tables. All the other fact tables only contains 1 date dimension.

purpose of master calendar:

* when you select 10th may 2011 .. you well see all the data of that day of each fact table, makes it possible to compare

* when i have a bar chart and a certain data doesnt contain any data, when my bar chart will miss days. i want to see the 0 values dates in my bar chart. I need a master calendar for that.

[#4]

what i meant was, in the one fact table containing the 2 date fields, must they both be May 10, or just one of them? what are those dates supposted to mean?

[amien]

i have now the fact table, which contains 2 fields with date (created and departure), linked to the master calender (via link table) on only 1 date (created). but i want in my pivot chart to both analyse the values on created date and departure date. including link to the master calender. in my opinion this can only be achieved to read the same fact table twice. one table will use the created date, the other table will use the departure date.

i was wondering if there is a different solution besides reading the fact table twice.

[#6]

You can try by linking the one date to the master calendar and the other in an expression with set analysis code linking dynamic to the the selected date in the master calendar.

[amien]

sounds good .. do you have an example?

[#8]

HI,

i tried it with something like this.

Sum (

                    {1<

                              FactTable.Date1={$(=Max(Calendar.CalDate))},

  FactTable.Date2={$(=Max(Calendar.CalDate))},

                    >}

Amount

)

Assumed that the dateformat is the same for all the different date fields, you can use this,

In this case you get all the Amounts when Date1 and Date2 are equal to the maximum value of CalDate.

You can look into the helpfile for more examples of the Set Analysis code. The basics are explained there.

I hope this is close to what you are looking for.

Regards,

Reyman

[Anonymous]

Using a seperate table (not coupled to other tables) for one of the date selections and then using Set Analysis works good for most of these situations.

When using 2 date selections on one date (start and ending date), this will not work ... since one selection rules out the other one. In this case you should consider adding 2 seperate date tables and only use set analysis (with big data amounts this is not really good for the performance!!).

Another way is doing some pre-loading in the LOAD script to add some extra data (link table or whatever you want) which contains the date combinations possible.

[amien]

@reyman

SUM({1<departuredate={">=$(=MIN(DateYYYYMMDD))<=$(=MAX(DateYYYYMMDD))"}>} DISTINCT value)

i'm using this one right now .. works great .. but only when the user selects 1 year and a few weeks/days.

i have a problem when a user selects 2 years and 1 weeknumber. i want to see the results of only week 2010-16 and 2011-16. now i get the range between 2010-16 and 2011-16

your set analysis i can't get it working