topic Re: Choose between two dates for dimension in QlikView

Choose between two dates for dimension

Mon, 09 Jan 2012 16:32:00 GMT

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.

Choose between two dates for dimension

swuehl — Mon, 09 Jan 2012 16:44:59 GMT

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?

Choose between two dates for dimension

Mon, 09 Jan 2012 16:54:44 GMT

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.

Choose between two dates for dimension

swuehl — Mon, 09 Jan 2012 17:02:46 GMT

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

Choose between two dates for dimension

Mon, 09 Jan 2012 19:55:06 GMT

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

Choose between two dates for dimension

swuehl — Mon, 09 Jan 2012 20:13:17 GMT

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

Re: Choose between two dates for dimension

Tue, 10 Jan 2012 10:07:24 GMT

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.

Choose between two dates for dimension

swuehl — Tue, 10 Jan 2012 10:55:40 GMT

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.

Choose between two dates for dimension

Tue, 10 Jan 2012 11:57:28 GMT

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

Choose between two dates for dimension

swuehl — Tue, 10 Jan 2012 12:02:04 GMT

Ah, I see, missed the source table view!

Thanks,

Stefan

Re: Choose between two dates for dimension

Tue, 10 Jan 2012 19:15:53 GMT

It worked that way!

Thank you very much for your help.

Best regards.