I feel like I'm missing something really simple, but I can't seem to get this set up. I'm trying to set up widgets for each month that show the total ticket sales for our museum admissions, the avg by day, the min we had by day, and the max we had by day for that month. I've tried moving things to categories and values and the best I get is one field saying 1, which is not right. I'm starting with setting up one widget for the month, but ultimately I'd also like to have one widget for all 12 of the fiscal year with all the figures. Thank you in advance for any ideas you may have.
Anne
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
Thanks, Chris! This is exactly what I needed and thank you for the explanation.