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
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.
You're welcome! I'm not sure I understand the question- are you trying to limit the perf day filter to only be in June? Or a subset of perfs within June?
I'd like to have a widget that shows the Min / Max / Avg for the current fiscal year. The formula you gave me for the current month worked perfectly, so I'm good for June and individual months moving forward. But I'd like to also calculate where we stand to date in the current fiscal year (which for us starts April 1). So rather than a breakdown by month, just one set of numbers for April 1 to today.
My first thought would be to duplicate the widget with the monthly breakdowns, but break it out by perf calendar year, rather than by month. Then you could adjust the formula to reference April 1st through yesterday. Would that work? I think the formulas would still work, but it wouldn't look to break things out by month.Keep the 'last' / 'count' / 'offset' portion of the formula as is, in general. It's going be looking at two things- the date you plugged in, and the count of days, offset 1. So if you have April 1st in the first bit, and 13/1 in the second:{"and": [{"from": "2021-04-01"},{"last": {"count": 13,"offset": 1}}],"custom": true}
It's going to be looking for dates after April 1st, but only the last 13 days (offset 1). I think you'd be looking for {"and": [{"from": "2021-04-01"},{"last": {"count": 366,"offset": 1}}],"custom": true}
That didn't do it. But I think what I need to do is change the Row in the Pivot table from Month to FY:
But the formula needs updating: { "explicit": true, "multiSelection": true, "members": [ "2022" ], "custom": true}
It looks like my bar chart might be right, however:
I'm a little unsure how to reply- were you able to get what you need? Were the yearly figures wrong, and that's how you knew the formula was off?
The yearly figure is giving me 1 as a MIN, which is a false return because it is counting it for the days that haven't had sales yet.
I would think the custom formula would weed out performances that hadn't happened yet- was it not doing so? Or could you add in a ticket count > 0 filter to work toward the same goal?
I do have a filter for ticket count >0. The custom formula you posted gave me an error. The starting formula is
{ "explicit": true, "multiSelection": true, "members": [ "2022" ], "custom": true}
And I'm not sure how to add in the offset to that. But it looks like the bar chart is giving me what I need, so I can live without the pivot table.
Not sure how to troubleshoot the formula error- glad you're getting the information you need, though!