Trying to build headcount over time chart

Question

Hi,I am new to Sisense and am trying to build an HR dashboard in Elasticube with flat files.I am trying to count employee IDs over time, for variable date slices. My approach has been to create a formula for the 'Value' that would only count the IDs that have a (hire date <= min date) and ((termination date is null) or (termination date >=  max date))I have a dim date table with every possible date that is being used on the x-axis. My issue is that when I try to build the formula and...

Ido Darnell · Accepted Answer

Hey brocksowa​  Firstly, welcome to the Sisense Community and to working with Sisense :) To achieve this I would recommend creating a custom column in the Employee table that holds these various date fields, not in the date dim.  The custom column should use a CSE/WHEN statement that creates a flag per your required condition and should look something like this: CASE WHEN Datediff([hire date] , [min date])<0 and (([termination date] IS NULL) or Datediff([termination date],[max date]...