I experiencing difficulty with "JOB End Dates". Calendar Quarter begin date = Calendar Quarter End date.. I was expecting to use this as a "between dates" function. Calendar Quarter Number is giving the exact "day" number of the end of the quarter. Is this by design? I would've expected 1 - 4...In a pivot, its easy to change but what is reasoning for "day". Thanks
The Date Dimensions all operate the same way: A date is represented in multiple ways.
For instance, the Job End Date April 25, 2012 is on a Wednesday (CalYr Job End Date Day of Week); it is the 4th day of the week (CalYr Job End Date Day Number in Week) (using US standards, internationl uses ISO 8601); it is in the 2nd quarter of the year (CalYr Job End Date Quarter Number), and is the 116th day of the year (CalYr Job End Date Day Number in Year).
Some of these fields act as shortcut. If you use the CalYr Job End Date Year Number = 2012, and CalYr Job End Date Month Name Abbr = 'Apr' then you could easily use those filters in an Excel Pivot Table to limit the data to jobs that have a Closed Date in April, 2012. This saves you from having to pick a list of specific dates, or create a clumsy date filter.
Other filters are helpful for cosmetic purposes. e.g. CalYr Job End Date Month End Date returns a value of 04/30/2012 (if the Job End Date is still April 25, 2012). Use this in a report that contains a header 'For the Period Ending:'
The last thing to remember is that the date dimensions act independently of each other. Job End Date refers to the Job End Date Field on a job - and has nothing to do with the Transaction Date. If you chose a job that had an end date of 4/25/2012, it is only filtering on that one field e.g. picking up jobs - and their transactions/dates - that have a Job End Date of 4/25/2012.
We have expanded our Guide on Dates and Date DIMENSIONS to include a grid that provides notes on each field within a typical Calendar Dimension.
BTW the Quarter information you referred to is incorrect and is a bug. This has been fixed.
Choose a location