Identify Null Roll-Ups in a Pivot Chart

NateBI

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)

Add Start of Week

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

Pivot columns:

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!

ST_Superman

@NateBI

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

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

DavidChurchman

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

ST_Superman

@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

@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

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

@NateBI

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

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

DavidChurchman

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

NateBI

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.