Analyzing information by date can be very useful, but not every source data set provides enough information to do this kind of analysis. In some scenarios, it will require importing a date table, which will act as a reference for all time queries.
Example- Employment Table
For this example, a user has imported an employment table that contains the following columns:
Employee Name, Start Date, End Date
This establishes the when an employee started and stopped working at a company. However, if we wanted to created a line chart that displayed days on the X axis, and charted the number of employees over time, it would not be possible with this data set. This is because the data set will not contain every individual date, assuming that there were days where the company did not hire or fire anyone. If every date is contained in your data set, use the instructions in this post to create the date data: https://support.sisense.com/entries/24044842-Creating-a-Date-Dimension-table
However, for this example, we would need to import a date dimension table.
Date Dimension CSV File
Attached to this post is a file called DateDim.csv. This contains day level information for dates ranging from January 1, 1901 to January 1, 2027.
After downloading the file, open the ElastiCube Manager, click Add Data-> CSV File, and select datedim.csv. In the preview, make sure the field Date is imported as a date-time field. If it isn't, check the culture setting below the preview pane, and select English (United States).
Using Custom SQL to Answer the Question
We still need to establish how many employees existed in the company on any given day. We can do this by creating a custom SQL table that counts the number of employees per day, or we can create a table that has a row for every day that an employee was employed by the company. We'll create the employee-day table for this example. Check the amount of history and the number of employees before choosing this option, to determine how many rows will get created.
The following pseudo-code would be used to create the new Employee-Date table
SELECT E.EmployeeName, D.Date FROM Employee E INNER JOIN DimDate D ON D.DateNum>=(10000*GetYear(E.StartDate)+100*GetMonth(E.StartDate)+GetDay(E.StartDate)) AND D.DateNum <= (10000*GetYear(E.EndDate)+100*GetMonth(E.EndDate)+GetDay(E.EndDate))
A few notes on the join clause:
- The date dimension file contains DateKey, which is an integer representation of the date value
- The employee table doesn't contain this integer value, and you are not able to compare date values in SiSense. The right side of the equality join uses a process that converts a date into an integer, so you can compare. Information on this process can be found here: https://support.sisense.com/entries/27924720-Numeric-Representation-of-Date-fields
Now, create a line chart that lists the days from this new table, and create a value that counts the number of employees on that day.
12/6/2015 Update- Added a column to contain Day of Quarter. This helps establish quarter to date calculations.