trying to create a beast mode calc

Member

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.

• Coach

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 any users posts that helped you.
• Coach

Hmm no problem @Abe ,

If you all ever need, I teach a course on how to use Domo:

Feel free to book 30 with me if you want to show me what you're trying to do:

John Le

You're only one dashboard away.

• Coach

Hi @Abe ,

Can you send a screenshot?

John Le

You're only one dashboard away.

• 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,

• 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

John Le

You're only one dashboard away.

• 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 any users posts that helped you.
• 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.

• Member

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,

• Coach

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 any users posts that helped you.
• Member

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?

• Coach

Hmm no problem @Abe ,

If you all ever need, I teach a course on how to use Domo:

Feel free to book 30 with me if you want to show me what you're trying to do:

John Le

You're only one dashboard away.