trying to create a beast mode calc

Abe

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.

MarkSnodgrass

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)

DashboardDude

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

DashboardDude

Hi @Abe ,

Can you send a screenshot?

Abe

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,

DashboardDude

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

MarkSnodgrass

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`)

Abe

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

Abe

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

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)

Abe

@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

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