Identify Null Roll-Ups in a Pivot Chart

NateBI
NateBI Contributor
edited February 2024 in Charting

Hi Folks,
Question:
How do I identify blank values in a pivot chart? I want to filter where all values are blank against any column in a pivot chart but no option. I can't do it in the ETL either because it needs to be dynamic based on a date selection. (dates are the columns)

Context

I have the fields 'People', 'Dates', and 'Hours'. The goal is to have a view pivoting dates to columns, summing the time under these dates, and viewing the people as the rows to see the people who have not got time against a particular week. This is achieved by a simple group by then pivot method

Method (Images)

Raw (ignore 'week' field)

image.png

Add Start of Week

image.png

group by Start of week and people, summing hours into Hours by week:

image.png

Pivot columns:

image.png


I've build this in Domo but stopped at the pivot step (since it needs to be dynamic).
I use a pivot chart with data that is at the grouped by start of week and people stage, using the start of week as columns.


The issue is in the pivot chart there is no option to filter the data to the rows where people have null against a week. Since it's so dynamic (end-users will use page filters for date ranges) I want to avoid hard code logic based on totals either.

All help is appreciated!

Best Answer

Answers

  • DavidChurchman
    DavidChurchman Coach

    You could create a beast mode that counts by week (or whatever is defining your columns) and filter by that.

    Something like:

    SUM(COUNT(People) fixed(by Week))

    Fixed function article: https://domo-support.domo.com/s/article/4408174643607?language=en_US

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

  • ST_Superman
    ST_Superman Domo Employee

    @NateBI

    Would there be a use case where a user would apply a date filter that would cut in the middle of a week? If not, you could create your null filter per user by week. This way there would be a flag in your dataset to indicate each week where a user had a null value.

  • NateBI
    NateBI Contributor

    @ST_-Superman-_ This sounds similar to @DavidChurchman 's suggestion - which is looking promising.

    The issue I have with this is that it requires the filter context of the week to display the nulls/blanks. So when placed in the values section of the pivot options it aligns with the summed hours but once brought into the filter section of the card there are no blank values.
    Reason for this:
    There are no people with zero time. Some just don't have it against a specific week. So external filters seem to only look for when time < 0 or equivalent - which doesn't exist.

  • ST_Superman
    ST_Superman Domo Employee

    Ah. In this case you will need to join your data to a calendar dataset so that you get at least one row of data for every individual. This will allow Domo to find values for any combination of dates, even if there is no data originally present.

    This has been written up in other solutions as well, I'm pretty sure there may even be a video tutorial about it floating around somewhere. Here is one post, I'll keep looking for the tutorial:

    https://community-forums.domo.com/main/discussion/48127/fill-in-missing-dates
  • ST_Superman
    ST_Superman Domo Employee
    Answer ✓

    @NateBI

    Here is a tutorial put together by @MarkSnodgrass with filling in missing dates.

    https://www.youtube.com/watch?v=9iNpzSL5V6U
  • DavidChurchman
    DavidChurchman Coach

    I'm sorry, I misunderstood the question. You want to be able to filter for where the value is NULL in the pivot table.

    You can't filter for rows in your dataset that don't exist (which is why it's null in your pivot table). I think you want to adjust the data so that there is a row of data for every person for every date of interest. To do this, in an ETL, you could join a calendar with your dataset to ensure all people have a row for all dates of interest. Then you could create a BeastMode that's responsive that you can filter on.

    CASE WHEN value IS NULL THEN 'FILTER ME' END

    For the calendar bit, there are different examples of how to do it, but this one looks translatable to your situation:

    https://community-forums.domo.com/main/discussion/53542/getting-a-sum-of-values-for-x-days-when-there-are-gaps-in-the-dates

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

  • NateBI
    NateBI Contributor
    edited February 2024

    Hi @DavidChurchman and @ST_-Superman-_
    Following @MarkSnodgrass's video - creates a many-to-many relationship for me. Constants on every day joining constants on every day. It's similar to what I have but I had added a constant 'dummy_person' against every calendar day, then left joined on the dates which should have been 1-2-1. The nulls would be the future.

Categories