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

D_Markley34
D_Markley34 Member
in Cards, Dashboards, Stories

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

Best Answer

  • marcel_luthi
    marcel_luthi Coach
    Answer ✓

    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.

Answers

  • trafalger
    trafalger Coach
    edited January 3

    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
    D_Markley34 Member

    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.

    Screenshot 2024-01-03 at 3.47.28 PM.png

    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
    marcel_luthi Coach
    Answer ✓

    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.

Categories