Calc Table - Calendar
This table is a fairly simple Calc Calendar Table.
- With AUTOCALENDAR
- With 1 FiscalYear column
- With Favorites Folder
- Splitting into Display Folders Text and Number Columns
createOrReplace
table CalcCalendar
dataCategory: Time
column Date
isKey
formatString: Short Date
displayFolder: 1. Favorites
summarizeBy: none
isNameInferred
sourceColumn: [Date]
annotation SummarizationSetBy = Automatic
annotation UnderlyingDateTimeDataType = Date
column Month
formatString: 0
displayFolder: 2. Calendar Date\2. Number Columns
summarizeBy: sum
isNameInferred
sourceColumn: [Month]
annotation SummarizationSetBy = Automatic
column 'Fiscal Year'
formatString: 0
displayFolder: 3. Fiscal Date\2. Numbers;1. Favorites
summarizeBy: sum
isNameInferred
sourceColumn: [Fiscal Year]
annotation SummarizationSetBy = Automatic
column Year
formatString: 0
displayFolder: 2. Calendar Date\2. Number Columns;1. Favorites
summarizeBy: sum
isNameInferred
sourceColumn: [Year]
annotation SummarizationSetBy = Automatic
column 'Month (MMM)'
displayFolder: 2. Calendar Date\3. Text Columns;1. Favorites
summarizeBy: none
isNameInferred
sourceColumn: [Month (MMM)]
sortByColumn: Month
annotation SummarizationSetBy = Automatic
column Day
formatString: 0
displayFolder: 2. Calendar Date\2. Number Columns
summarizeBy: sum
isNameInferred
sourceColumn: [Day]
annotation SummarizationSetBy = Automatic
column 'Is Before This Month'
formatString: """TRUE"";""TRUE"";""FALSE"""
displayFolder: 4. Flags
summarizeBy: none
isNameInferred
sourceColumn: [Is Before This Month]
annotation SummarizationSetBy = Automatic
column 'Is Current Fiscal Year'
formatString: """TRUE"";""TRUE"";""FALSE"""
displayFolder: 4. Flags
summarizeBy: none
isNameInferred
sourceColumn: [Is Current Fiscal Year]
annotation SummarizationSetBy = Automatic
column 'Is Previous Fiscal Year'
formatString: """TRUE"";""TRUE"";""FALSE"""
displayFolder: 4. Flags
summarizeBy: none
isNameInferred
sourceColumn: [Is Previous Fiscal Year]
annotation SummarizationSetBy = Automatic
column 'Is Current Calendar Year'
formatString: """TRUE"";""TRUE"";""FALSE"""
displayFolder: 4. Flags
summarizeBy: none
isNameInferred
sourceColumn: [Is Current Calendar Year]
annotation SummarizationSetBy = Automatic
column 'Is Previous Calendar Year'
formatString: """TRUE"";""TRUE"";""FALSE"""
displayFolder: 4. Flags
summarizeBy: none
isNameInferred
sourceColumn: [Is Previous Calendar Year]
annotation SummarizationSetBy = Automatic
column 'Is Current Month'
formatString: """TRUE"";""TRUE"";""FALSE"""
displayFolder: 4. Flags
summarizeBy: none
isNameInferred
sourceColumn: [Is Current Month]
annotation SummarizationSetBy = Automatic
column 'Is Previous Month'
formatString: """TRUE"";""TRUE"";""FALSE"""
displayFolder: 4. Flags
summarizeBy: none
isNameInferred
sourceColumn: [Is Previous Month]
annotation SummarizationSetBy = Automatic
column 'Year Month Key'
formatString: 0
displayFolder: 2. Calendar Date\2. Number Columns
summarizeBy: count
sourceColumn: [Month Key]
annotation SummarizationSetBy = Automatic
column 'Relative Month'
formatString: 0
displayFolder: 4. Flags
summarizeBy: sum
isNameInferred
sourceColumn: [Relative Month]
annotation SummarizationSetBy = Automatic
column Quarter
displayFolder: 2. Calendar Date\3. Text Columns
summarizeBy: none
isNameInferred
sourceColumn: [Quarter]
annotation SummarizationSetBy = Automatic
column 'End of Month'
formatString: General Date
displayFolder: 2. Calendar Date\2. Number Columns
summarizeBy: none
isNameInferred
sourceColumn: [End of Month]
annotation SummarizationSetBy = Automatic
column 'Week of Year'
formatString: 0
displayFolder: 2. Calendar Date\2. Number Columns
summarizeBy: sum
isNameInferred
sourceColumn: [Week of Year]
annotation SummarizationSetBy = Automatic
column Weekday
formatString: 0
displayFolder: 2. Calendar Date\2. Number Columns
summarizeBy: sum
isNameInferred
sourceColumn: [Weekday]
annotation SummarizationSetBy = Automatic
column 'Is Current or Past Months'
displayFolder: 4. Flags
summarizeBy: none
sourceColumn: [Is Current or Past Month]
annotation SummarizationSetBy = Automatic
hierarchy 'Date Hierarchy'
displayFolder: 2. Calendar Date\1. Hierarchy
level Year
column: Year
level Month
column: Month
level Date
column: Date
hierarchy 'Fiscal Date Hierarchy'
displayFolder: 3. Fiscal Date\1. Hierarchy
level 'Fiscal Year'
column: 'Fiscal Year'
hierarchy 'Calendar Hierarchy'
displayFolder: 1. Favorites
level Year
column: Year
level 'Month (MMM)'
column: 'Month (MMM)'
partition CalcCalendar = calculated
mode: import
source = ```
VAR Today = TODAY() -- Modify for testing
VAR MonthStartFiscalYear = 10
RETURN
ADDCOLUMNS(
/*CALENDAR(
DATE ( 2022, 1, 1 ),
DATE ( 2027, 1, 1 )), */ --use it if CALENDARAUTO is not an option
CALENDARAUTO(),
"Year",YEAR([Date]),
"Quarter", "Q "&QUARTER([Date]),
"Month", MONTH([Date]),
"Month (MMM)", FORMAT([Date], "MMM"),
"Day",DAY([Date]),
"Fiscal Year", YEAR([Date]) + IF(MONTH([Date]) >= MonthStartFiscalYear, 1, 0),
"End of Month", EOMONTH([Date], 0),
"Week of Year", WEEKNUM([Date]),
"Weekday", WEEKDAY([Date]),
"Is Current or Past Month", IF([Date] <= EOMONTH(TODAY(), 0), "Yes", "No"),
"Is Before This Month",FORMAT([Date],"YYYYMM")<FORMAT(Today,"YYYYMM"),
-- Current/Previous Fiscal Year flags
"Is Current Fiscal Year",
VAR CurrentFiscalYear = YEAR(Today) + IF(MONTH(Today) >= MonthStartFiscalYear, 1, 0)
RETURN YEAR([Date]) + IF(MONTH([Date]) >= MonthStartFiscalYear, 1, 0) = CurrentFiscalYear,
"Is Previous Fiscal Year",
VAR CurrentFiscalYear = YEAR(Today) + IF(MONTH(Today) >= MonthStartFiscalYear, 1, 0)
RETURN YEAR([Date]) + IF(MONTH([Date]) >= MonthStartFiscalYear, 1, 0) = CurrentFiscalYear - 1,
-- Current/Previous Calendar Year flags
"Is Current Calendar Year", YEAR([Date]) = YEAR(Today),
"Is Previous Calendar Year", YEAR([Date]) = YEAR(Today) - 1,
-- Current/Previous Month flags
"Is Current Month",
YEAR([Date]) = YEAR(Today) && MONTH([Date]) = MONTH(Today),
"Is Previous Month",
VAR PrevMonthYear = IF(MONTH(Today) = 1, YEAR(Today) - 1, YEAR(Today))
VAR PrevMonth = IF(MONTH(Today) = 1, 12, MONTH(Today) - 1)
RETURN YEAR([Date]) = PrevMonthYear && MONTH([Date]) = PrevMonth,
-- Month Key (YYYYMM format)
"Month Key",
YEAR([Date]) * 100 + MONTH([Date]),
-- Relative Month (0 = current month, negative = past, positive = future)
"Relative Month",
(YEAR([Date]) - YEAR(Today)) * 12 + (MONTH([Date]) - MONTH(Today))
)
```
annotation TabularEditor_TableGroup = 03. Dimension Tables
No RepliesBe the first to reply