Best Of

You can do this with a formula tile and a group by tile. In your formula tile, create a column called TimePeriod and use the LAST_DAY() function to normalize your dates to a single day in each month so that you can group by month. Your formula would look like this:

LAST_DAY(pickdate)

Add a group by tile and group by TimePeriod. Create an aggregated column for each plant and use the Add Formula to create the aggregation. For example, create an aggregated column called Seattle and have your formula look like this:

COUNT(DISTINCT CASE WHEN `plant` = 'Seattle' THEN `picknumber` END)

Do this for each plant and you will have a distinct count for each location for each month.

Hope this helps.

Quick filters to do not pay any attention to the sorting properties, unfortunately. They are treated as strings and will always sort ascending. Your best bet is to have your values be Year-Month, with the month always being 2 digits. You can do that like this:

DATE_FORMAT(`datefield`,'%Y-%m')

This should return it as 2022-04 for April 2022.

Thanks @MarkSnodgrass !..This works..Appreciate your help!

I think your issue is around the double quotes in parts. Here is an example of how I did one similar where ClaimNumber is my variable name:

CONCAT('<a href="https://mydomoinstance.domo.com/page/954056803?pfilters=[',
    '{%22column%22:%22ClaimNumber%22,%22operand%22:%22IN%22,%22values%22:[%22',
    `ClaimNumber`,
    '%22]}',
    ']" target="_blank">',
    `ClaimNumber`,
    '</a>')

I found it worked best to break things up into more concat statements than technically necessary to deal with the quotes.

try adding IFNULL() around each of those like this:

IFNULL(Inside Count,0) + IFNULL(Outside Count,0) + IFNULL(Phone Count,0)

If you have blanks that aren't nulls, you may want to do some additional cleanup in a formula tile prior to the group by tile.