Choose between two dates for dimension

[#1]

If I have a record with a start date and an end date, with both dates connected to a date hiearchy, is it possible to choose which of the dates to present in a chart that uses that date hiearchy as a dimension?

For example, having the same bar chart twice, with the same expression and the same total, but in of the charts the values are presented by start date and in the other the values are presented by end date. Is this possible?

Thank you in advance,

Best regards.

[swuehl]

Well, I would say this should be possible.

Could you detail a bit more your data model, especially how your two fields start and end date are connected to your date hierarchy? Is your date hierarchy a master calendar or could you describe this a bit more, too?

[#3]

Ok, I have a calendar table with the following fields:

calendarID, complete date; year; month; week; day

So far each record had only a calendarID that connected to the dates in calendar table.

Now I need to have two dates per record, and I'm trying to find what is the best way to do this.

[swuehl]

You could look into using a linkage table (search the forum for examples, I assume there are lots of). This should also allow you easily to select either start date or end date per graph (e.g. using set analysis).

[#5]

Thanks for the help, but I couldn't make the linkage table work.

In my data model I would have 2 fields from the same table (start_date_id and end_date_id) connecting to 2 fields the linkage table, and that way it doesn't work...

[swuehl]

Please have a look e.g. at this sample:

http://community.qlik.com/message/116832

Hope fully this explains the concept a bit better. In the sample of John, you just need to add a master calendar linked to Date field of the link table.

In your setting, you could then use DateType field to select start or end date in your charts.

Have a nice evening,

Stefan

[#7]

I tried that, but it can't make it work.

I'm attaching my example to see if anyone can find why it doesn't work.

[swuehl · Accepted solution]

Interesting, I haven't seen two links between two tables without a synthetic key so far! Not sure if this is a bug or what the effect of this is.

Anyway, I think you shouldn't use two Keys to link your fact table and the link table, only one. Please look again at John's example in the referenced thread (I am referring to the last posting by John Witherspoon.

Maybe create a unique key in your fact table (recno()?), then create your link table based on that key, creating two lines (start date and end date) per original fact table key, and setting an appropriate date type.

[#9]

Hi Swuehi,

It is not a bug.

You are seeing two links becuase the table view selected in table viewer is source table view, just change it to internal table view and you will see the same synthetic table and key

Hi joaoltghenriques,

Why don't you link fact to one key and then that one key to the start date and end date

.....

Ashutosh

[swuehl]

Ah, I see, missed the source table view!

Thanks,

Stefan