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.

[chancekbarkley]

Morning Neil,

It looks like you've go the right idea with potentially using a link table, an island calendar, or a concatenated fact table, but your model seems to be a bit of a combination of the first 2 in a way that I don't think will get you there.

If you simply need a count of transactions, i would probably create a table with TransactionID, TransType, and Date, where TransType specifies historical or future and date correlates to that.  You can join this back to your model on TransactionID, and then link Date to a master calendar rather than an Island Calendar.  When it comes to your chart, you can use set analysis to specify the transtype and do a count distinct.

I'll see if i can work it up based on your example and post it here.

Hope this helps.

-CB

[santhosh_k_n]

Hi,

Try using FirstSortedValue function with sort weight as the calender field(suffix - for previous value)

BR

SK

[lironbaram]

have a look at the attach example

maybe it'll give you a direction

[maxgro · Accepted solution]

see the attachment

[chancekbarkley]

Example Attached.

[tinkerz1]

Thanks Maxgro and Liron, this has really gone very far into helping me understand how to build data map and using dimensions with simple statements

[tinkerz1]

Hi Chance,

I don't see the attachment, can you repost please, I am really keen to see your data model.

Thanks,

Neil.

[chancekbarkley]

Odd you don't see it...re-attached

[tinkerz1]

Hi Chance,

Thanks for the script, it helps understanding how to normalize a data model.

One reason for a creating a date island is to have a continuous run of dates, when dealing with future data its important to see the months without any data.

"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."

With your script and others I have managed to get a working data model and script.

Thanks.