How do you build a Single Value card when using a variable?

D_Markley34

I am trying to produce a single value card that shows the number of active employees at a specific point in time. The data set columns included:

• employeeId
• employeeFirstName
• employeeLastName
• employeeStatus
• employeeStatusStartDate
• employeeStatusEndDate (this is null for the current status)
• locationName
• regionName
• rowFingerprint (unique identifier)

There is a beast mode calculation called ActiveUniqueUsers:
COUNT(DISTINCT case when employeeStatuslike 'ACTIVE' then employeeId end)

An employee can have one of three possible status at any time and over time they can move between any of the 3 statuses.

I have created a date variable called StatusAsOf that allows the user to select a specific date. I use the variable in my beast mode calculation (filterHistorical):

CASE
WHEN StatusAsOf >= MAX(employeeStatusStartDate)
AND StatusAsOf <= MIN(IFNULL(employeeStatusEndDate,DATE_ADD(CURRENT_DATE(), INTERVAL 1 day))) THEN 'Include'
ELSE 'Exclude'
END

I have filterHistorical as a filter on the visualization set to value of "Include"

I have been able to verify that the the filterHistorical works by using a table visualization that contains the employeeStatusStartDate and employeeStatusEndDate

marcel_luthi

I might be overlooking a couple of things, but what you need your variable to do is to find the entry in your table where the value falls between the employeeStatusStartDate and employeeStatusStartDate , so you don't need aggregations, as with single value cards that will affect the whole dataset as you're not breaking it down to any level.

Try using the following beast mode for the filter:

CASE
WHEN `employeeStatusStartDate` <= `StatusAsOf` AND (`employeeStatusEndDate` IS NULL OR `employeeStatusEndDate` >= `StatusAsOf`)THEN 'Include'
ELSE 'Exclude'
END 

Keep in mind this logic works as long as there are not 2 active statuses for the same employee at the same time. In the Mega Table this should yield the same result as the previous one but should also work for the single value card.

trafalger

If you switch it to a table card, do you see employees? Otherwise the issue might be with your aggregations, I don't think you need them if each employee has those columns on them.

D_Markley34

Yes, if I change it to table view I can get to the employees that have a max status start date less than the status as of variable date and the min end status end date greater than the status as of date variable.

For the single value visualization the setup I have the value set to ActiveUniqueUsers. For the optional label name I have tried:

• Null - returns no data
• employeeId
• employeeStatus - returns no data
• employeeStatusStartDate
• employeeStatusEndDate (this is null for the current status)
• locationName
• rowFingerprint (unique identifier)

None of them return the same results as I see in the table view which matches what is in the data set

marcel_luthi

I might be overlooking a couple of things, but what you need your variable to do is to find the entry in your table where the value falls between the employeeStatusStartDate and employeeStatusStartDate , so you don't need aggregations, as with single value cards that will affect the whole dataset as you're not breaking it down to any level.

Try using the following beast mode for the filter:

CASE
WHEN `employeeStatusStartDate` <= `StatusAsOf` AND (`employeeStatusEndDate` IS NULL OR `employeeStatusEndDate` >= `StatusAsOf`)THEN 'Include'
ELSE 'Exclude'
END 

Keep in mind this logic works as long as there are not 2 active statuses for the same employee at the same time. In the Mega Table this should yield the same result as the previous one but should also work for the single value card.