Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
brucewhanya
Contributor III
Contributor III

Past and future data

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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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)

View solution in original post

11 Replies
alextimofeyev
Partner
Partner

Keep your fact tables separate.

These resources may be useful:

Generic keys

Canonical Date

brucewhanya
Contributor III
Contributor III
Author

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

swuehl
MVP
MVP

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

brucewhanya
Contributor III
Contributor III
Author

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
MVP
MVP

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?

brucewhanya
Contributor III
Contributor III
Author

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
MVP
MVP

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.

brucewhanya
Contributor III
Contributor III
Author

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
MVP
MVP

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)

View solution in original post