Min / Max/ Avg ticket sales by month

Hi Anne,

The raw Ticket Count value is always either a 1 or a 0, so the Max and Min of Ticket Count will always be 1 and 0. Since you're wanting to group the calculation by day within months, we need a formula that will aggregate the 1s and 0s up to dates, and then return the avg, min, max of those results. I also filtered on Ticket Count > 0, because my local database has dates without sales that were skewing my results. You may not need that filter.

AVG ( [Days in Performance Date] , [Total Ticket Count] )

This says, for each Performance Date, get the SUM([Ticket Count]) or [Total Ticket Count], and then from those results, return the average. Replace AVG with MIN and MAX for the other two values. Once that's working, you can group by month in Categories. 

AvgMinMax.dash

One more question -- when I set it up for the current month and include the ticket count >0 filter, it is giving me 1 for the MIN, which seems to be what it is counting for the days that haven't happened yet. Is there a way to make it work before the month is over? Thanks, again!

Would you not want it to look at days that haven't happened yet? One workaround might be to restrict the Perf Date to only include dates up through yesterday. I've found this quite useful in a lot of contexts!

If you filter by Perf Date, then select 'Day', you can use this little custom formula to include FROM a certain date (you tell it when) TO yesterday (defined by the "offset":1.

{
"and": [
{
"from": "2021-03-11"
},
{
"last": {
"count": 366,
"offset": 1
}
}
],
"custom": true
}

I always like to click "Test" before 'OK', to make sure it's looking correct. It'll only pull in the first 10 of any set of days, but it helps.

Using the above configuration, this will always filter only for perfs within 'January 4th through yesterday'. I've found this quite helpful in avoiding having to update formulas day after day. This will weed out the perfs which haven't happened yet.

It seems to require a bit of priming- make sure to select 'Time Frame', then 'Days', and 'Yesterday' or 'Tomorrow', so that it knows we want days, then go to the 'Advanced' tab and paste in your customer query!

Does that help? It's helped me quite a bit.

Thanks, Nathan! That makes sense and seems like what I need. I'll work on setting it up and let you know if I come across any difficulties.

That worked like a charm for the monthly widgets. Thank you! I'm also trying out widgets that have a running total for the year, by month, and I can't seem to figure out how to get the current month to show without the error. I got this far by adapting your forumula:

{
"and": [
{
"from": "2021-04-01"
},
{
"last": {
"count": 13,
"offset": 1
}
}
],
"custom": true
}

Is there a way to get June in there with the correct numbers? Or would this one need to only include complete months (which I can live with).

Correction -- I don't need it to show the month-by-month breakdown, but rather the running total for the year.

Correction -- I don't need it to show the month-by-month breakdown, but rather the running total for the year.