Pivot Table Static First Column

user027926
user027926 Member
in Charting

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!

Answers

  • GrantSmith
    GrantSmith Coach

    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

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • user027926
    user027926 Member

    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
    GrantSmith Coach

    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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • user027926
    user027926 Member

    @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
    MarkSnodgrass Coach

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

    image.png


    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • user027926
    user027926 Member

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

  • MarkSnodgrass
    MarkSnodgrass Coach

    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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • user027926
    user027926 Member

    @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!

Categories