trying to create a beast mode calc

Abe
Abe Member
in Beast Mode

Hello, Can you please assist on helping me with creating a beast mode calculation.

I've been trying to create one but it doesn't seem to calculate what I need.

The column dimension I have is a ticket count of how many tickets were created on a date entered for each day of the month.

The rows are different departments. The pivot table displays how many tickets are created for each department each day of the month.

Now I need a column that calculates the average of the total tickets dependent on how many days have passed for that month.

ex. if 10 days have passed for the month, I need to get that average of the total tickets for each row for the past 14 days.

and so forth, 14 days, 18 days, 19, 20…

any assistance is greatly appreciated.

Best Answers

Answers

  • DashboardDude
    DashboardDude Coach

    Hi @Abe ,

    Can you send a screenshot?

    Circle.png

    John Le

    You're only one dashboard away.

    Click here for more video solutions: https://www.dashboarddudes.com/pantry

  • Abe
    Abe Member

    Sorry, Here is the screenshot of the data- I need an average of each of the rows whether its 5 days into the month or 10, 15, so on.

    I started with this calc- SUM(date_entered)/DAYOFMONTH(date_entered) but that doesn't work and i've been playing around with it but getting no where,

    image.png

  • DashboardDude
    DashboardDude Coach

    Hi @Abe ,

    So this is going to be tough if every column is a date. You really need to have one column as date and then you can do some pivot action. I'd recommend doing these steps:

    1. Do a dynamic unpivot to put all the dates in one row
    2. Then you can write a formula to do some like case when datediff(curdate,date_entered) ⇐ 5 then ticketnumbers end
    3. Rest and contemplate life

    Let me know if that makes sense otherwise I can do some time with you

    Circle.png

    John Le

    You're only one dashboard away.

    Click here for more video solutions: https://www.dashboarddudes.com/pantry

  • MarkSnodgrass
    MarkSnodgrass Coach

    Have you tried using a window function to get a running total? It would look something like this:

    1. SUM(SUM(`ticket_number`)) OVER (PARTITION BY board_name ORDER BY `date_entered`)

    **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.
  • Abe
    Abe Member

    @MarkSnodgrass , thank you for the advice. I tried using the formula that you provided but the SUM of the ticket number seems like it's adding the actual ticket number and not the count, so I changed it to

    SUM(COUNT(TicketNbr)) OVER (PARTITION BY board_name ORDER BY date_entered)

    But the Avg column for each date is totaling each day that passes. What would I need to change on the formula that would Avg the number of day it passes from the total count of tickets. Example- 12/3 date would have 28 total tickets / 3 days = 9.3 avg for the first row.

    image.png

  • Abe
    Abe Member

    Hi @DashboardDude ,

    Thank you for your advice.

    I tried to create a new board per your advice but I could not figure out how to get a dynamic unpivot table.

    Sorry, we just started Domo few months ago and still getting to know the system,

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    Perhaps if you divided that by the day of the month, like this:

    SUM(COUNT(TicketNbr)) OVER (PARTITION BY board_name ORDER BY date_entered)

    / DAYOFMONTH(date_entered)

    **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.
  • Abe
    Abe Member

    @MarkSnodgrass

    Thank you sir! I believe that did the trick. But for some reason, Domo is not able to save the card after adding the new formula. Does the system not recognize the formula?

  • DashboardDude
    DashboardDude Coach
    Answer ✓

    Hmm no problem @Abe ,

    If you all ever need, I teach a course on how to use Domo: https://www.dashboarddudes.com/

    Feel free to book 30 with me if you want to show me what you're trying to do: https://www.dashboarddudes.com/domo-help

    Circle.png

    John Le

    You're only one dashboard away.

    Click here for more video solutions: https://www.dashboarddudes.com/pantry

Categories