beast mode calculation

Abe · January 19

Hi Everyone,

Thank you for assisting me the last time and hoping the community can assist me once again.

I have a table that shows the rows of sites along with columns of how many tickets are associated to the sites and the ticket numbers.

The data I need are only for sites that have 3 or more tickets.

I've tried filtering (aggregation) out sites with count that is greater than or equal to 3 but somehow no data populates.

Is there a beast mode calculation that I can create to only show sites with 3 or more locations while showing the ticket numbers?

Any assistance is greatly appreciated.

ST_-Superman-_ · January 19

@Abe

You could create a beastmode that would count the number of tickets per customer site:

SUM(COUNT(DISTINCT `TicketNbr`) FIXED (BY `Site_Name`))

Then, you should be able to use this as a filter for your card.

ST_-Superman-_ · January 19

@Abe

Did you try bringing the field into the table? Or just as a filter? Try adding it to the table to see if it is calculating correctly.

ArborRose · January 19

As a fixed field (test)….and filter.

ArborRose · January 19

Your filter appears to be on the site name, not on the # of tickets.

ArborRose · January 19

A beast mode calculation might be something like the

SUM(CASE WHEN TicketNbr IS NOT NULL THEN 1 ELSE 0 END)

Then put that calculation into the filter where fieldname is greater than or equal to 3.

Abe · January 19

Thank you ArborRose, I tried doing it that was as well but no data will show for that filter as well.

ST_-Superman-_ · January 19

@Abe

You could create a beastmode that would count the number of tickets per customer site:

SUM(COUNT(DISTINCT `TicketNbr`) FIXED (BY `Site_Name`))

Then, you should be able to use this as a filter for your card.

ArborRose · January 19

https://community-forums.domo.com/main/discussion/comment/86919#Comment_86919

Your TicketNbr field seems to contain things other than ticket numbers. What are the COUNT 1 / xxxxx values?

When doing a condition search for a value, I often write it as

{fieldname} IS NOT NULL AND {fieldname} <>''

I use this because sometimes values are NULL, other times they are blank or empty. Which is not the same thing. In your field however, it looks like ticket number contains something more than a ticket number.

ArborRose · January 19

What is your last field doing? Seems like you should be removing that last field from the table. Then put the filter in for >=3. You would then get the list of sites with 3 or more tickets.

GrantSmith · January 19

@ArborRose The last column is showing 1 / count because it's the summary row on a table, it's not data within the dataset.

I agree with @ST_-Superman-_ a Fixed function filter should work to have you only see sites with >= 3 tickets.

Abe · January 19

@ArborRose

the fields are site name, then count of ticket number and then ticket number - no aggregation so it can lists the ticket numbers

I was able to get sites that had 3 or more tickets but then I needed to add the ticket numbers as well and thats where i'm getting stuck on.
I noticed that as well i was looking at the data- count 1/ xxxx value. The value seems to be the same when it shows count 1 or count 2 and so forth.

How should I add this calc to the one provided- {fieldname} IS NOT NULL AND {fieldname} <>''?

ArborRose · January 19

Abe · January 19

@ST_-Superman-_

I used the beast mode calc that you provided but I was not getting any data

ArborRose · January 19

Using a calculation instead of the aggregate…

Abe · January 19

@ArborRose

Yes, when I do that, I can get the drill down when I click on the data. But I need to send this as a scheduled report via excel so they would like to see all the data on the excel sheet.