beast mode calculation

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.

Tagged:

Best Answers

  • ST_-Superman-_
    Answer ✓

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


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • ST_-Superman-_
    Answer ✓

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


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • ArborRose
    ArborRose Contributor
    Answer ✓

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

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

Answers

  • ArborRose
    ArborRose Contributor

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

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

  • ArborRose
    ArborRose Contributor

    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.

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

  • Abe
    Abe Member

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

  • ST_-Superman-_
    Answer ✓

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


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • ArborRose
    ArborRose Contributor

    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.

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

  • ArborRose
    ArborRose Contributor

    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.

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

  • @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.

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

    @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
    ArborRose Contributor

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

  • Abe
    Abe Member

    @ST_-Superman-_

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

  • ArborRose
    ArborRose Contributor

    Using a calculation instead of the aggregate…

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

  • Abe
    Abe Member

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

  • ST_-Superman-_
    Answer ✓

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


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • ArborRose
    ArborRose Contributor
    Answer ✓

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

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

  • Abe
    Abe Member

    ST_-Superman-_

    That worked! I used it as a filter and it did provide me to data that I needed.

    Thank you!

  • Abe
    Abe Member

    Thank you @ArborRose and @ST_-Superman-_

    Your assistance is greatly appreciated! Hope you both have a great weekend!