Trying to build headcount over time chart
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 select the hire date it will count the numbers of days etc in hire date so it will say something like [Days in Hire Date].
Do you have a suggestion on how to build widgets that filter for date ranges, and the available data is hire/termination dates?
Thanks!
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 ENDNote: 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
Feel free to subscribe to our newsletter to stay up to date with the latest QBeeQ news
