topic Re: Past and future data in QlikView

Past and future data

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Past and future data

alextimofeyev — Sat, 25 Jun 2016 11:01:50 GMT

Keep your fact tables separate.

These resources may be useful:

Generic keys

Canonical Date

Re: Past and future data

Anonymous — Sat, 25 Jun 2016 11:10:24 GMT

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

Re: Past and future data

swuehl — Sat, 25 Jun 2016 11:26:55 GMT

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)?

Re: Past and future data

Anonymous — Sat, 25 Jun 2016 12:50:53 GMT

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

Re: Past and future data

swuehl — Sat, 25 Jun 2016 15:34:54 GMT

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?

Re: Past and future data

Anonymous — Sat, 25 Jun 2016 17:15:26 GMT

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.

Re: Past and future data

swuehl — Sun, 26 Jun 2016 10:20:34 GMT

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.

Re: Past and future data

Anonymous — Wed, 29 Jun 2016 19:16:40 GMT

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.

Re: Past and future data

swuehl — Wed, 29 Jun 2016 20:00:34 GMT

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)

Re: Past and future data

Anonymous — Wed, 29 Jun 2016 20:40:58 GMT

Ok thank you will try that

Re: Past and future data

Anonymous — Sat, 30 Jul 2016 10:55:17 GMT

forgot to mark this one, worked well thank you.