Hi all,
I need help with a couple of widgets, that to be honest, I'm not even sure where to start - I'm struggling with which dates to use where, but everything should be based around performance date as opposed to order or attended dates.....I'm trying to recreate some graphs that have been created by our research team in excel & I'd like to take the manual work from them if possible by adding it to our exhibitions dashboard. The graphs currently look like the 2 below - but I'm open to anything that will give them the same data.
2. Rolling four week average ticket sales by day of the week
thanks for any advice!
jo
Hi Jo,
The first one is easy enough with bucketing formulas. With the ORDER DATE > Day of Week Sort on Categories of a Column chart, add a Value for each 4-week period, where the Total Ticket Count is filtered by ORDER DATE > Date being in the desired 4-week range... here's a start with 3 buckets.
For the second one, do you have the formula that produced it in Excel? Is each date limited to that date and the 3 prior occurrences of that day of week? Is it then the average of those days across the 4 weeks? If yesterday was Monday, then total of yesterday's tickets, plus the 3 prior Mondays, divided by 4?
Thanks Chris - that makes total sense - I tired to take that logic and attempt the other graph required but the dates are tripping me up again - the formula for the current excel chart is simple - there is a list of ticket count per day multiplied by the 4 week period then divided by 28 days for the ave.
Really appreciate the help!
There's an undocumented function for a running sum of previous periods: RPSUM(). It takes a value and a count of previous periods, where the type of period (month, day, etc.) is defined by whatever you have on the widget axis.
The orange is an RPSUM ( [Total Ticket Count] , 28 ), showing a running sum of the prior 28 days from the date on the axis. A nice thing about this RPSUM is that it's not just the 28 prior visible periods in the widget, but the 28 prior periods regardless of whether there was data. So if you have 1-Feb followed by 27-Feb because there were no sales on the dates in between, in will include those missing dates in the 28 prior.
RPSUM ( [Total Ticket Count] , 28 )
The black line is that same formula divided by 28, similar to your Excel formula.
RPSUM( [Total Ticket Count] , 28 ) / 28
Thanks so much! You're a legend!
HI Chris Wallingford, at my orchestra we don't have performances every day, and I want to report on the past 90 days sales, for our main production strands only, so will need to divide by the number of unique performances within those productions within the running 90 day period in order to see our average sales. Any pointers on how to achieve a formula for that would be much appreciated!
PS Chris Wallingford I was the person who asked about running sums in your Innovation studio last week. We're trying to assess how audiences are building back after the pandemic, using a 90 day running period to iron out repertoire/programme fluctuations.
Melanie, I think what your looking for is the same numerator, set to 90 days, and instead of a hard coded value for the denominator, something that counts unique performances in the 90 days prior to the given date on the axis...
RPSUM([Total Ticket Count],90) / RPSUM([# of unique Performance ID],90)
Thanks Chris, it gives me a feasible-looking line chart (showing it going down to 0 during the UK lockdown, and then showing growth back after the pandemic, but it appears to only be giving me one data point at the start of each financial year on 1 April. I'd like to see 12 data points each FY and I'm not sure how to achieve this - I have Days in Date for the X-Axis as we're looking at 90 days in your formula, with your formula in the Values. (Changing it to Months or Years in date gives different values but still only one data point per year.)
And can I check that the top line of the formula is looking at sales for performances in the past 90 days (which is what I want), rather than sales in the past 90 days (which is not what I want - I don't think I was very clear on that!)?
Hi Melanie,
What Days in Date do you have on the X-Axis? In earlier comments we were working with the Performance Date, but it sounds like you might be looking for Order Date. If you have Order Date then the RPSUM of Total Ticket Count will only include tickets in orders on the order date of the x-axis and the 89 prior dates. The RPSUM of the # of performances will only include performances associated with tickets in orders on the given order date and the 89 prior dates. Does that sound aligned with what you're wanting. Related... we just learned that this could be a factor here: Running Sum Overcounting, requiring a slight shift in approach.