Neutral Date Dimension

[#1]

Hi all,

I currently have a data model (completely denormalized) which contains multiple facts (Leads, Surveys etc...)

Each of these facts has its own associated date dimension which we report it against (Leads = DateSetup, Surveys = SurveyDate...)

What we want to do is create a report with a date dimension which all facts can be reported against, e.g. on 24-Sep we show Leads with a SetupDate of 24-Sep and Surveys with a SurveyDate of 24-Sep. Basically they are being displayed against a netural date field.

What is the best way to do this? Should we be focusing on enabling this in the load script or can we do this in the front end via set analysis (we have a calendar data island created).

Any help is appreciated.

Thanks

Adam

[#2]

Any help would be appreciated!

Don't think i can use the date fields from my calendar data island as a dimension in my report?

Thanks

Adam

[#3]

I've created a quick example file if this helps,

The table "HELP!" contains my efforts so far.

I want CalendarDate to be my dimension (and the dates for this to always be shown).

As expressions, I want SurveyQty and LeadQty to be reported against this CalendarDate (where SurveyDate = CalendarDate and LeadDate = Calendar Date).

Thanks

[hic · Accepted solution]

You can do something similar to what you're attempting by using CalendarDate as as dimension in the chart. Just CalendarDate as dimension - no calculated dimension.

Then, as expression you can use Sum( if(CalendarDate=SurveyDate,SurveyQty) ) etc.

The above is a good way to get several dates on the same chart dimension. But if you want to make one selection in one date field and another selection in another date field, you need to load the calendar several times. See more on this blog post.

HIC

[#5]

Thanks Henric,

Thought it might be as simple as this!

Cheers

Adam