Hi,
I'm building a dashboard to track our package sales and compare those sales to previous years. If I use Days Since Onsale, and fudge all my performances in each season to have a Default Sales Date of that years subscription onsale then I can get some nice line graphs (although I have to manually fiddle with the viewing window so that the the length of the previous years doesn't make the chart unreadable), but for other types of widgets, such as bar graphs and pivots, I desparately need some kind of "Onsale to Date" Year to Date doesn't work well because our onsale dates are often off by over a calendar week, and some years recently much more than that. Is there such a field, or a way to use Days Since Onsale to filter results to match the current seasons timeframe?
Hi Gawain,
I've been coming at this one from a number of angles and landed on something that seems to work. First off, forgive my max order days since on-sale in the current season being 1,010 (bad local data), but know that the max in earlier seasons is even larger :)
For a pivot widget with days since onsale on Rows and Season Fiscal Year on columns...
...a formula like this could work...
IF( [Max Order Days from On-sale]<= ([Max Order Days from On-sale],[Season Fiscal Year = 2022],ALL([Order Days from On-sale])) ,RSUM([Total Ticket Count]) ,NULL)
...in combination with a formula like this...
IF( [Max Order Days from On-sale]<= ([Max Order Days from On-sale],[Season Fiscal Year = 2022],ALL([Order Days from On-sale])) ,1 ,NULL)
...where that formula is set with a Value type filter > 0, and then the value disabled from display in the widget.
Converting this to a line chart by simply switching chart types doesn't work. The widget filter is in essence a second value, and with a field in the Break by panel, a secondary value is not supported. So we have to switch from using Season Fiscal Year in the Break by to using a value per Season Fiscal Year in the widget. The formula for Season FY = 2022 might look like this...
IF( [Max Order Days from On-sale]<= ([Max Order Days from On-sale],[Season Fiscal Year = 2022],ALL([Order Days from On-sale])) ,RSUM(([Total Ticket Count],[Season Fiscal Year = 2022])) ,NULL)
If you want to try and make this more sustainable, you can consider using Season Current Fiscal Year Offset instead of Season Fiscal Year, and label the values with relative FY names like "This FY" instead of 2022.
Cheers,Chris
Chris!
I am trying to do the same. I did the same thing, where I set all the Default start dates for each show to the Renewal start date. I have one season that I can't get to work with the Order Days Since Onsale on my X-Axis. I can get it to work and look great with Days in Date, RSUM all the constituents over time. I got the pivot working first. But then it gets all wonky with Order Days Since Onsale. Do you have any thoughts on the difference between the two fields on the X-Axis? I am getting it to work for the two subsequent seasons, but can't figure out where to add any filters for my 21-22 season.