Past and future data

[Anonymous]

I need advise on data modelling. I am building a dashboard that should have events that happened and events that are planned, these are two fact tables and I could concatenate them however when analyzing and slicing by date i want one date list box to show only valid dates for past events and another date list box that shows only dates valid for future events. if the fact table has both past events and future events is that possible or maybe there is a better way to deal with the two fact tables?   

Thank you in advance

[alextimofeyev]

Keep your fact tables separate.

These resources may be useful:

Generic keys

Canonical Date

[Anonymous]

I will have a look I hope the links will be useful, will let you know

[swuehl]

Maybe just create a flag field for past / future dates to filter the date field / records of your concatenated fact table.

Do the two fact tables show mostly the same information (fields)?

[Anonymous]

They have 70% of the fields expect for fields like times assosiated with the events. If i create a flag field as you suggest in the concatenated fact will i need another copy of the date because i surely want the use to select dates with valid data and avoid charts with no data displayed

[swuehl]

Shouldn't it be possible to use this flag also to filter your charts or control what to show?

Not really sure if I understand what you are trying to achieve, though.

Would it be possible that you create and upload a small sample QVW?

[Anonymous]

Ok here is is a quick sample the object in the real situation is to find out how well rooms are being utilized and  how fully booked are the rooms in the future to allow more booking etc.

[swuehl]

Ok, so what would be the exact issue with just concatenating the tables (renaming fields to common field names e.g for City etc.)?

Note that you can filter your date list box or date chart dimension to show only past or future dates, if this is your main concern (e.g using the mentioned flag field or just a condition in the UI).

If you want more help on this, then please post also the input files to allow a reload of the QVW.

[Anonymous]

yes that was my main concern of having two date tables I will try your suggestion and yes i want to filter the monthYear, day and week list boxes to show past dates and do the same on a different sheet to show future dates where the future table will be . correct me if Im wrong, I will use a date flag like 1 for future dates and 0 for past dates then filter using calculated dimension? or is it set analysis.

[swuehl · Accepted solution]

You can do it both ways, either using a calculated dimension like

=If( Flag = 1, Date)

or using set analysis in your aggregation functions like

=Count({<Flag = {1}> DISTINCT EventID)