Past and future dates in the same table

[tinkerz1]

Hi,

I have a table with fields that hold the date of the transaction (historical) and future date for following up a call or letter.

What I need to do is chart the historical and future dates on a chart using the same timeline.

How do I go about this, what is the best practice?

Is it best to create a new table in the load script, concatenating the Transdate(historical) and Transdatefollowup(Future) field?

All I am going to chart is the count if transactions.

The issue is that the future dates will have gaps in the month, so I need to make that show by using calander table with no breaks in months.

How do I also include a calender table in, my tables are producing SYN table.

[tinkerz1]

I have a further question about adding in dates(months) that have no transactional data.

If I make a calender and link that to a transactional or fact table, I can select dates, but  I need to show months with no data when pivoting or charting, because there are no dates in the fact table how do I get a timeline that is not broken due to no transactions in that month?

Thanks.

[chancekbarkley]

Neil,

My point was that you dont need a "Date Island" in my version, rather just a "Master Calendar".  A date island is typically the same as a master calendar, but as the name suggests it is not connected to the model.  It is useful when you have multiple date fields to filter on and is leveraged via expressions in the front end.

In my example, I have a master calendar that will produce a date for every day between your min and max dates, but it is connected to the model.  Since there is a record for every day and not an associated value, the dates with no value will show up blank unless you hide the empty dimensions via object properties.

This should also address the question you just posted.

Hope this helps.

-cb

[tinkerz1]

Hi Chance,

I had to leave my laptop at work but I am reading up on master calendar tables.

If I am reading correctly I would concatenate month - year in the master calendar at the lowest level of data, ie transactional data(daily) and then I can pivot and chart from the month - year concatenated field to group into monthly data.

Then I can add in monthly budget data to the fact table and flag that loaded table 'budget' data; as long as I have a date field in the uploaded budget table with a date in the same format and column as the transaction data; I can then plot 2 different aggregations levels in the same pivot table or chart.

Is that how you would handle this data?