Date Formulas

From DataSelf Knowledge Base
Jump to navigation Jump to search

Notes


SYSTEM_Calendar

Lists every day from 1/1/1990 to 12/31/2030. Provides a source of date keys, in date format and numeric serial numbers for daily, weekly, monthly ("Period"), quarterly, and annually. Changing the fiscal year properties in the source (..\DataSelf\DataSelf ETL\Project\ProjectName\OtherSourceDBs\DSTIMESERIES.xls) can accommodate any fiscal year configuration. The "Basexxx" fields are Fiscal Year; the "Calxxx" are Calendar. If no fiscal year modificatons are made, the fiscal year is calendar year.

Serial Keys

Fields labeled "xxxSKey" are static serial keys, imported when the fiscal year was set up. Fields labeld "xxxSKeyR" are relative keys, recalculated at every refresh, based on the system clock. Today has _BaseDateSKeyR = 0; yesterday is = -1; tomorrow = 1. The same logic is applied to _BasePeriodSKeyR, _BaseQuarterSKeyR, _BaseYearSKeyR, _BaseWeekSKeyR. Using relative keys allows reports to always reflect the desired period (often "last month"), so the user does not have to change the dates with every viewing.



SYSTEM_Date

Rolling Periods

Rolling Period Shift

This sets the Rolling Periods'(where RollingXXXKey = -1) ending dates to be other than Today (the computer clock at refresh).Change the formula to be as many days before today as you wish. The ETL is usually refreshed after midnight, so the data being reported is as of yesterday. The default value = 1, thus the default Rolling Periods (where RollingXXXKey = -1) to have an ending date of yesterday. This is also used in the WorkDaysDone and WorkDaysLeft formulas.

Work Days Done / Work Days Left / Work Days In Period

Sums up from the SYSTEM_Calendar the number of completed work days, from the beginning of the current periods (month, quarter, or year) to Yesterday (per system clock at refresh). That is, it sums the SYSTEM_Calendar.CalWorkDay_1Yes_0No, which is based on the entries in SYSTEM_Holidays and SYSTEM_Weekdays tables. By default, it sums up work days BEFORE today for the WorkDaysDone calculation (Filter is SYSTEM_Calendar._BaseDateSKeyR < 0). Change the formula's filter to include today, if desired (SYSTEM_Calendar._BaseDateSKeyR <= 0). WorkDaysLeftXXX = WorkDaysInXXX - WorkDaysDoneXXX

These are available for reports (and are usually used for calculated measures) in the DSA Data Sources in the Work Days measures folder, which comes from the _C_DataRefreshedAsOf SQL view.


SYSTEM_Holidays

SYSTEM_WeekDays

SYSTEM_DaysOutBuckets

These lists link to the SYSTEM_Calendar._BaseDateSKeyR to group the dates into buckets such as the 30/60/90 Days Overdue buckets used in Receivables or Payables aging reports. Each bucket has a Key field used for sorting, and the text description. For additional buckets not created here, it is recommended that the user create a new ETL table,importing buckets from an Excel file. Use ..\DataSelf\DataSelf ETL\Project\ProjectName\OtherSourceDBs\DSTIMESERIES.xls DaysOutBuckets tab as a model.

SYSTEM_RefreshHistory

SYSTEM_Filter_Parameters

A legacy system, seldom used.