Trying to build headcount over time chart

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])>0) THEN 1
ELSE 0
END

Note: you may need to change the conditions to be <> 0 depending on the required logic.

Then in the front end dashboard formula editor you can use a measured value which basically allows one to filter a value in the formula level instead of the widget or dashboard level. The value of this measured value should count the Employee IDs while the filter should be the flag=1 of the custom column you created in the previous step. That way you will be counting only the employees which fall into your criteria.

The formula should look something like this  

(Count(Employee_id),[filter to flag=1])

For more on Measured values, please see this article

If the date fields are in different tables you may need a more complex custom column code or even a custom table expression. LMK if you'd any further clarification on that.

If you have any other questions don't hesitate to reach out,

We're always here to help 

Ido from QBeeQ

QBeeQ - Gold Sisense Partner

[email protected]

Feel free to subscribe to our newsletter to stay up to date with the latest QBeeQ news