Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I want to simply choose a date range. Let’s say I want to see all my data within January. With one timestamp field, there is no problem. Because I can set this date selection/filter for that specific timestamp field. And I will get all data related to that timestamp within January.
But what when I have e.g. three timestamps: timestamp1, timestamp2 and timestamp3. All three timestamps are related to different data. So I have three line diagrams with the three timestamps as dimension. What I want is, when I select e.g. a month in one of the diagrams, the other two diagrams should also show only the data from that selected month.
Is there any overall calendar, where all timestamps are related to? So that I can select a date range e.g. a month or week and I get all the data related to all the different timestamps within that selected range?
UPDATE
To be more clearly:
Table1:
DateTime1 | Data1 |
---|---|
2017-01-01 00:00:01 | 0.1 |
2017-01-01 00:00:03 | 0.5 |
2017-01-01 00:00:08 | 0.2 |
Table2:
DateTime2 | Data2 |
---|---|
2017-02-01 10:00:01 | 205 |
2017-03-01 11:05:43 | 307 |
2017-04-01 08:13:27 | 163 |
Table3:
DateTime3 | Data3 |
---|---|
2017-06-12 06:12:01 | 36.5 |
2017-06-12 14:39:02 | 152.1 |
2017-06-13 23:16:10 | 687.0 |
The data has nothing to do with each other. There are no relations between the three tables. I just want to select a month, a week or a specific day and see all the related data from the three tables.
Is it possible to have a calendar and connect DateTime1, DateTime2 and DateTime3 with that calandar? Or is it possible to create a common calendar from these three date fields?
There are many blogs written by HC on this topic. You can refer them.
Why You sometimes should Load a Master Table several times
Regards,
Neha
Hi Chris
Please use below extension for calendar selection.
http://branch.qlik.com/#!/project/5697a878dcc497f80ed514bf
Many Thanks
Karthik
The canonical date approach seems to be very useful here. But in all the examples I could found, the data were related together. In the blog post you mentioned for e.g. the data is related via OrderID. But in my example the data is not related.
I just want to select a month and get all data within that month from all tables. Where the tables have nothing to do with each other.
Hi Christoph,
- you can rename all your date-time fields with the same name so every table will be linked on this field
- you can even create a CalendarTable linking all tables by this Date-Time-Field to get common months, year, etc. fields
- or you can join all your tables like
Date-Time-Field, Data1Field, Data2Field, Data3Field
Date-Time, Data 1, Data 2, Data 3
BR,
Thomas
Hi Christoph
Its a very good question. As I find it confusing when there are calendars for lots of dates. And I have seen other users doing filters based on the wrong date. And then believing Qlik is a rubbish product ...
But there isn't a simple answer. It depends on circumstances. Qlik Data Manager uses derived dates so its the way Qlik seem to prefer. One calendar for every date.
But I try to use Canonical dates if its possible. But sometimes its not. For example one table with the finer grain might miss all the relevant details from the parent table. In this situation I try to add the missing records to the finer grain table. I have explained all of this on Henric's excellent Canonical Date blog post (BTW Henric doesn't agree with using a Canonical calendar in this way but I don't agree)
Another options is to use a Date Island. Where a calendar is set up but not joined to the other tables. But joined through set analysis. I have used this at times as a temporary solution but prefer the canonical date option.
Another option is to concatenate the three tables. And add a new field to distinguish the tables. Then use this field in set analysis as required
Please see the attached file. You need to change the date format for master table.
Regards,
Neha