tmendesf wrote: Hi all ...I have some tables (in each table I have 2 or more date fields) and I want to have a single master calendar for every date field in order to be able to have, for example, a chart and can filter by year. How can I do that?... |
I'll post my most recent example of this sort of thing, which handles multiple dates from multiple tables as requested, so it may be what you need. I say "may" because this approach applied to multiple tables CAN cause loops in the data structure, in which case you'll need either underlying data model changes or just a completely different solution (such as the island calendar, also discussed below). Since you can't bring up the example, I'll also give you the script and the chart definitions below.
Here's the script:
SET DateFormat='YYYY-M-D';
Table1:
LOAD * INLINE [
ID, Seq, ActivityDate, Value1
1, 1, 2011-5-10,10
1, 2, 2011-5-12,2
2, 1, 2011-5-12,20
3, 1, 2011-6-15,40
];
Table2:
LOAD recno() as ID, * INLINE [
DepartureDate, ArrivalDate, Value2
2011-6-15,2011-6-16,5
2011-6-15,2011-6-17,10
2011-6-16,2011-6-16,20
];
OtherTable:
LOAD recno() as OtherID, * INLINE [
OtherDate, OtherValue
2011-5-12,2
2011-6-15,3
2011-6-17,5
];
Link:
LOAD
ID
,Seq
,ActivityDate as Date
,'Activity' as DateType
RESIDENT Table1
;
CONCATENATE (Link)
LOAD
ID
,DepartureDate as Date
,'Departure' as DateType
RESIDENT Table2
;
CONCATENATE (Link)
LOAD
ID
,ArrivalDate as Date
,'Arrival' as DateType
RESIDENT Table2
;
CONCATENATE (Link)
LOAD
OtherID
,OtherDate as Date
,'Other' as DateType
RESIDENT OtherTable
;
Calendar:
LOAD *
,date(monthstart(Date),'MMM YYYY') as Month
;
LOAD date(makedate(2011,5,1)+recno()-1) as Date
AUTOGENERATE 61
;
IslandCalendar:
LOAD *
,date(monthstart(IslandDate),'MMM YYYY') as IslandMonth
;
LOAD date(makedate(2011,5,1)+recno()-1) as IslandDate
AUTOGENERATE 61
;
So in the script above, Table1, Table2 and OtherTable are my actual data tables. The intent of the rest of the script is to add to this model without changing it, and for the additions to handle the linkage to a single generic calendar. The Link table is where all this linkage happens, where all the magic happens, and then the Calendar table is the generic calendar. The IslandCalendar table is for the alternative I mentioned, which is probably a much more common solution to this kind of problem.
My example has three example charts in it. The first is a straight table:
Dimension: Date
Expression 1: sum({<DateType={'Activity'}>} Value1) // set analysis needed because ID is shared
Expression 2: sum({<DateType={'Departure'}>} Value2) // set analysis needed because ID is shared
Expression 3: sum(OtherValue) // set analysis not needed because OtherID not shared
The second chart is exactly the same except for using Month as a dimension.
The third chart is a pivot table defined like this:
Dimension 1: ID
Dimension 2: DepartureDate
Dimension 3: ArrivalDate
Dimension 4: ActivityDate
Expression 1: sum(Value1)
Expression 2: if(dimensionality()=1,sum(Value2))
Then we have the island calendar alternative. As stated in a text box on the sheet:
"An island calendar is disconnected from the rest of your data. It's a possible alternative if using the data model solution above causes loops in your data structure. The disadvantage is largely performance, as every expression must use an if() to link the island date back to whatever date you actually care about, which can be a very slow process compared to direct links in the data model. The expressions would typically be more complex with an island calendar as well. In the example, the expressions are of similar complexity, but that's because our example is particularly complex, sharing an ID across multiple tables that each have dates. This is probably atypical.
The bottom example chart is the same either way, as it is referring directly to the underlying dates rather than either of our master calendars."
The first chart with the island calendar is defined like this:
Dimension: IslandDate
Expression 1: sum(if(ActivityDate=IslandDate,Value1))
Expression 2: sum(if(DepartureDate=IslandDate,Value2))
Expression 3: sum(if(OtherDate=IslandDate,OtherValue))
The second chart is just like the first but with IslandMonth as the dimension. And the third chart is exactly the same as the third chart with the previous solution.
