topic Past and future dates in the same table in QlikView

Past and future dates in the same table

tinkerz1 — Fri, 30 Oct 2015 12:04:01 GMT

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.

Re: Past and future dates in the same table

chancekbarkley — Fri, 30 Oct 2015 12:11:17 GMT

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

Re: Past and future dates in the same table

santhosh_k_n — Fri, 30 Oct 2015 12:12:15 GMT

Hi,

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

Re: Past and future dates in the same table

lironbaram — Fri, 30 Oct 2015 12:27:12 GMT

have a look at the attach example

maybe it'll give you a direction

Re: Past and future dates in the same table

maxgro — Fri, 30 Oct 2015 12:35:30 GMT

see the attachment

Re: Past and future dates in the same table

chancekbarkley — Fri, 30 Oct 2015 12:43:13 GMT

Example Attached.

Re: Past and future dates in the same table

tinkerz1 — Fri, 30 Oct 2015 13:00:30 GMT

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

Re: Past and future dates in the same table

tinkerz1 — Mon, 02 Nov 2015 12:29:09 GMT

Hi Chance,

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

Thanks,

Neil.

Re: Past and future dates in the same table

chancekbarkley — Mon, 02 Nov 2015 12:45:00 GMT

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

Re: Past and future dates in the same table

tinkerz1 — Mon, 02 Nov 2015 13:28:52 GMT

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.

Re: Past and future dates in the same table

tinkerz1 — Mon, 02 Nov 2015 15:37:42 GMT

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.

Re: Past and future dates in the same table

chancekbarkley — Mon, 02 Nov 2015 15:45:26 GMT

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

Re: Past and future dates in the same table

tinkerz1 — Mon, 02 Nov 2015 22:43:30 GMT

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?