• Replies 3 replies
  • Subscribers 961 subscribers
  • Views 96 views
  • Users 0 members are here
Related

Bottom 5? or 20? But with a minimum.

Katie Springer
$organization

Hello, TAMATO!

I'm working on pulling zip code data for our marketing director and I was easily able to make a top 20 widget, but I'm also looking to do least performing zip codes with a minimum of something like 100 tickets. Does anyone know a way to do this? 

Thanks in advance!

Parents
  • $organization

    Hi Katie,

    Good work around. 

    To offer an alternative example, I took a copy of the Production Sales pre-built dashboard, and edited the Top 5 Postcodes by Ticket Count widget. I changed the Widget Filter for Postcode from Top 5 to Bottom 20, and then edited the Ranking formula. Instead of using a simple Sum of Ticket Count (or [Total Ticket Count]), I suggest a conditional formula that excludes postcodes with fewer than 100 tickets, which might look like this:

    IF ( [Total Ticket Count] >= 100
     , [Total Ticket Count]
     , NULL
     )

    Since this formula is being evaluated in the context of each distinct postal code, it will calculate the total ticket count within each postal code, and if the count is < 100, return a NULL (no data) for those postal codes. Returning a 0 won't work as that's still a value that would be among the bottom 20, whereas NULL is more like, make it not exist. For those where the ticket count is >= 100, it will still return their total ticket count as before.

Reply
  • $organization

    Hi Katie,

    Good work around. 

    To offer an alternative example, I took a copy of the Production Sales pre-built dashboard, and edited the Top 5 Postcodes by Ticket Count widget. I changed the Widget Filter for Postcode from Top 5 to Bottom 20, and then edited the Ranking formula. Instead of using a simple Sum of Ticket Count (or [Total Ticket Count]), I suggest a conditional formula that excludes postcodes with fewer than 100 tickets, which might look like this:

    IF ( [Total Ticket Count] >= 100
     , [Total Ticket Count]
     , NULL
     )

    Since this formula is being evaluated in the context of each distinct postal code, it will calculate the total ticket count within each postal code, and if the count is < 100, return a NULL (no data) for those postal codes. Returning a 0 won't work as that's still a value that would be among the bottom 20, whereas NULL is more like, make it not exist. For those where the ticket count is >= 100, it will still return their total ticket count as before.

Children