Pivot Table Static First Column

user027926

Hi all,

I am working on a pivot table that has all Sales Reps and Counts the number of call that they've made. It accomplishes this by COUNT of Phone call, COUNT of Zoom meeting, etc. (each its own column)

My columns are : Date / Call Category / Count of each type of Call.

Problem: When I filter by date, anyone who has no communication on that date disappears. I want them to remain with either a null or zero next to their names. I already clicked on 'properties --> general --> fill empty cells with zero, but that only fills empty cells with zeroes for the reps that have other cells filled, the other reps still don't show.

Thanks in advance for your help!

GrantSmith

You’ll need to have a record for each rep for each day. To do this you can import the Calendar.csv from the Domo Dimensions connector. Then run a select columns on your dataset to pull just the rep name. Run that through a distinct tile. Then for both of those use two separate Add Contant tiles to add a column called Join Column and set the value to 1. Then use a join to join both datasets based on the join column. You can filter your dates to ignore ones in the future and any dates prior to the earliest sales rep date. Take this joined column dataset and left join your original dataset to it you should now have records for every rep for every day. You can then use a formula tile to do a COALESCE function to default any nulls to 0

user027926

Thank you @GrantSmith ! This was really helpful and SUPER clear (and something that I've wanted to better understand for a while, so thank you!)

Here's the catch-- the card is comprised of values that are all COUNTs. Meaning having a zero in a field won't change the fact that there are sometimes just zero counts and therefore the row won't appear. How do I make COUNT rows appear when they are zero?

Thanks!!

GrantSmith

You can skip the last step of using COALESCE and just leave the values of NULL in place. Then count won't include those and should give you 0 for those.

user027926

@GrantSmith --yes, that's what I did, but because each column is just a COUNT, the rows where the count=zero are not showing

MarkSnodgrass

@user027926 To fill in blanks on the pivot table, try going to the chart properties -> general and select "Fill Empty Data Cells With 0"

user027926

@MarkSnodgrass -- thanks, that's what I tried first. No success

MarkSnodgrass

you might try using SUM to do your counting. For example, you can do something like this:

SUM(CASE WHEN `column` >= 1 THEN 1 ELSE 0 END)

You will want to change the WHEN statement to what would work in your situation, but this might get the results to display the way you want.

user027926

@MarkSnodgrass . Thanks. I tried that and I also tried changing all nulls to zeroes in the ETL. Also tried doubling up on the ETL and making a case within a case. For some reason, it's not changing. All other ideas are welcome :)

Thanks!