Linking 2 calendars

[#1]

Hi,

In my document I am using two calendars, one calendar for several tabs and another for one tab.

What I'm trying to do is link them so say for example selecting month June and year 2010 on the HHCalendar and then selecting another tab that uses the other calendar, the selection I made will transfer on the other calendar.

I'm trying to link the year section and month section.

Calendar 1- Month field name is ConsumptionMonth, Year= ConsumptionYear
Calendar 2-Month = HHReadMonth, Year=HHReadYear.

Is it possible to link these so that the selection will appear on both calendars?

Thanks in advance,
mckay9999

[vgutkovsky]

McKay,

Yes this is possible. I would recommend you do this using a date island. The technique involves creating a separate island (not linked to anything) in your script. This island would contain all possible dates. You would show this island in the front-end to users to enable them to make selections. You would then need to modify all your expressions to compare the actual dates to the selections in the island fields. FYI, this will affect performance slightly (or maybe more)...

Regards,

[syed_muzammil]

Hi,

As far as i understood your question, you can use the actions for the year and month from the first calendar. This will automatically select the year and month of the second calendar on the next tab.

Regards,

Syed Muzammil.

[johnw]

I've used actions to "link" two calendars like this, where there were reasons I couldn't link them in data. I doubt it applies to this case, though. I'm guessing that we could have records like this:

ConsumptionDate, HHReadDate,...
01/15/2010 , 03/30/2010,...

If you select January, and you're on a "Consumption" tab, you want to select this record. If you linked the dates together with an action, it wouldn't select this record. It would only select records where BOTH dates were in the selected range.

Vlad's suggestion of a Date Island works, and is a common solution. But as he says, there are performance penalties for it, and on large data sets, it can become VERY slow. Also, you have to code the condition into every single expression, like sum(if(ConsumptionDate=CalendarDate,Value)). So it's a coding and maintenance headache if there are a lot of expressions on your tabs.

I THINK we can get around those problems with the following, though I haven't tried it. DO make a third calendar as suggested. But then connect the dates from it to your actual dates through a date type field. So for example:

CalendarDate, DateType , ConsumptionDate, HHReadDate
01/15/2010 , Consumption, 01/15/2010 ,
01/15/2010 , HHRead , , 01/15/2010

Set date type using an action when you switch to a tab. So if we're on a tab that should be using the ConsumptionDate, we've set the DateType to 'Consumption' when we entered the tab. So if we've selected January, that will map through the 'Consumption' date type to ConsumptionDates in January. Then if we switch to a tab that should be using HHReadDate, an action sets the DateType to 'HHRead' as we enter the tab. Now Janaury no longer maps to ConsumptionDates in January, but instead to HHReadDates in January.

Hopefully it works like it sounds.

[vgutkovsky]

John,

Interesting idea. But wouldn't doing it this way create a loop? I'm assuming that ConsumptionDate and HHReadDate each exist in a table that is, in turn, linked on a key to other data. By linking these dates through the new table, wouldn't that complete the loop? Or maybe I'm misunderstanding...

[johnw]

Well, I'm trying it now, and no, it didn't create a loop. But the result is just as bad - we're selecting null for the other date, which maps to NO rows, not to all rows. So it fails utterly.

I'm betting there's a way to do it with data, but it certainly isn't what I wrote!

One possibility is explicitly listing all possible dates instead of the null, but that could be a memory problem, so doesn't seem like a good solution. I'll keep thinking about it.

Edit: Yeah, explicitly listing all possible dates seems to work, but isn't practical due to it taking the square of the number of days for storage. That might be fine for a year or three, but ten or a hundred? Forget it.

Edit: Oh, and the action approach to synchronize dates should work anyway. You just need three calendars instead of two. When a date field is selected or changed, copy its settings based on the current tab. I don't think you can detect the current tab directly, but it's easy to set a variable or field value to indicate which mode you're in when you switch to the tab. A DateType, basically.

[johnw]

Perhaps the best way to avoid having the square of the dates in rows is just to link the new date field directly to your main fact table by ID instead of linking to the other dates. That's probably more rows for many practical situations, but should always be relatively small in memory requirements compared to your main table since it's two small integer fields. Well, plus a text field, but with only two values, so I'm confident that it compresses well.

It's also an approach I've used before, which always gives me a little more confidence in it.

So here's a working example. I have 50 orders from January. Each order has both an order date and a ship date. There is also a Date field on a calendar. The date field is connected to each order ID through a DateType field of 'Order' or 'Ship'. OnActivate of a sheet, we select the appropriate value in the field. A hidden (visible in the example) list box enforces that there is always one selected value, so that a clear won't mess up the data.

[#8]

guys,

thanks for all your feedback, it's very helpful.

I'm concerned about affecting the performance of the document.
The document I am working on has approx 1000+ lines of code thus their is a huge amount of data( a reload takes nearly 3 hours). Should I attempt to link these calendars or am I wasting my time just to end up slowing the document vastly?

[vgutkovsky]

If your document is large then the date island solution is probably not appropriate. Try John's idea, which should be faster.

Regards,

[johnw]

It's a trade off. A date island will load quickly, but the chart performance will probably be very bad for such a large amount of data. The script approach I posted will add some time to your load (random guess: five to ten minutes), but the chart performance should be very good, and chart maintenance will be simpler. I almost always sacrifice script speed in favor of chart performance. If script speed is critical, I'd probably address that as a separate issue, as there's usually a whole lot of tuning that can be done.