Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am new to Master Calendar and need some help understanding it. I have two dates in my table:
CreatedDate - which is the date the item was created
ChangeDate - which is the date the item is updated
I want to have two charts:
1. One that shows me the total number of items created by month and year
2. One that show me the total number of items changed by month and year
I would like to have two list boxes:
One that has the months and one that has the years. Based on those selections I want the charts mentioned above to reflex those results. Is this something a Master Calendar can accomplish and can someone give me so pointers.
David
Using master calendar as data island will do the job. The charts dimension is from the master calendar, and expressions compare created or changed date against the dimension. See attached.
It is simple and reliable, but maybe not the best from the performance point if the data set is large.
What are the other Date dimensions you need ? If you need other Date dimensions for both dates, you need to create the 2 calender tables.
The three date dimension I will need is for any of the two dates list I will select
year
month
year and month
once they are selected the charts will reflex the results. So it sounds like two calendar tables is the way to go.
In that case, Better go for 2 calender tables one for Created Date and another for Changed Date or you can directly add these fields in the same table because its only 4 new fields.
Month(Created_Date) AS CreatedYear,
Year(Created_Date) AS CreatedMonth ,
For the Month Year use , calculated Dimension like "CreatedMonth & CreatedYear" in the chart dimension.
Create the same thing for Changed_Date as well.
You can consider creating a link table between your fact table (assuming having a FactID, CreatedDate, ChangedDate in your table) and a master calendar like
CrossTable (Type, Date) LOAD FactID, CreatedDate, ChangedDate Resident FactTable;
This will transform the table to a table with FactID, Type and Date fields, Date field linking to your master calendar.
Then filter your records using Type in the charts, e.g. to filter ChangedDate chart, dimension from Master calendar and as expression:
=sum({<Type = {ChangedDate}>} Amount)
Using master calendar as data island will do the job. The charts dimension is from the master calendar, and expressions compare created or changed date against the dimension. See attached.
It is simple and reliable, but maybe not the best from the performance point if the data set is large.
thank you Michael. That helps me understand it better. I now just need to add additional years and I am set. I will play around with it.
David