Top clients requesting devices

Options
Coni
Coni Member
in Magic ETL

Good day,

I want to create a beast mode statement that gives me a list of all the clients who have requested more than two devices each month. The final outcome would look like this:

Request Date

Individual_ID

# of Requests

2024-Jan

12345

2

56789

3

11234

4

56789

2

2024-Feb

12345

5

56789

4

11234

5

56789

2

So far this is what I have.

case WHEN YEAR(request_date)=YEAR(CURRENT_DATE())-1 and MONTH(request_date)=MONTH(CURDATE() and (COUNT(DISTINCTindividual_id) >=2) THEN 'Top Requestor' ELSE 0 END

Thank you in advance,

Best Answers

  • ColemenWilson
    ColemenWilson Coach
    edited February 20 Answer ✓
    Options

    You can just apply filters to the card, you don't need to include the logic in the beastmode. All you need is a table card filtered to the current year and filtered with COUNT(individual_id) > 2. Then in your table card add Request Date, Individual ID and Top Requester. Then aggregate the Top Requester field with a count.

    Screenshot 2024-02-20 at 10.57.50 AM.png

    If I solved your problem, please select "yes" above

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓
    Options

    I believe you can do this without doing any beast modes, but using the aggregation filter capabilities. Here's how:

    1. In your table card, drag in Request Date, Individual ID, and then Individual ID a 2nd time, but choose to Aggregate by Count.
    2. In your Date Range Filter, set your date range to use Request Date and graph by month.
    3. Drag Individual ID to the filters section and choose Aggregation and choose to Aggregate by count where it is greater than or equal to 2.

    Your table card should now the results you are looking for.

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

Answers

  • ColemenWilson
    ColemenWilson Coach
    edited February 20 Answer ✓
    Options

    You can just apply filters to the card, you don't need to include the logic in the beastmode. All you need is a table card filtered to the current year and filtered with COUNT(individual_id) > 2. Then in your table card add Request Date, Individual ID and Top Requester. Then aggregate the Top Requester field with a count.

    Screenshot 2024-02-20 at 10.57.50 AM.png

    If I solved your problem, please select "yes" above

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓
    Options

    I believe you can do this without doing any beast modes, but using the aggregation filter capabilities. Here's how:

    1. In your table card, drag in Request Date, Individual ID, and then Individual ID a 2nd time, but choose to Aggregate by Count.
    2. In your Date Range Filter, set your date range to use Request Date and graph by month.
    3. Drag Individual ID to the filters section and choose Aggregation and choose to Aggregate by count where it is greater than or equal to 2.

    Your table card should now the results you are looking for.

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

Categories