Matching Sales Day by Day Comparison with previous Festivals

Hello Iain,

Have you looked at filtering the first widget by the Fiscal Year to Date Flag = Yes? Here's the description from help:

A flag to indicate whether a day within a fiscal year is within the "to date" time frame of the current date.

For example, if your fiscal year starts in January, and the current date is October 15, any date from January 1st through the October 14th in any fiscal year is considered in the "to date" time frame and will have the flag set to Y. This allows you to compare the current fiscal year to a different fiscal year as of the current year's date. You can filter or break by dates being before or after where you are in the current fiscal year.

Because data is loaded into Tessitura Analytics nightly, the "to date" time frame always ends yesterday. In other words, you can think of the "to date" time frame as being up to and including yesterday. Also note that, if the current date is the first day of the fiscal year, then all days of the year are considered "to date" (because the data in the system goes up to and through yesterday, the last day of last year).

As for Yesterday Year over Year, given that your grouped by Season, we probably need a formula that uses a conditional CASE statement and then based on the year of the row, offsets the Time Frame = Yesterday filter by 365 days.

CASE WHEN [Max Season Fiscal Current Year Offset] = 0 THEN ( [Total Ticket Count] , [Days in Order Date Time Frame = Yesterday] )
WHEN [Max Season Fiscal Current Year Offset] = -1 THEN ( [Total Ticket Count] , [Days in Order Date Advanced offset: 365] )
WHEN [Max Season Fiscal Current Year Offset] = -2 THEN ( [Total Ticket Count] , [Days in Order Date Advanced offset: 730] )
...etc...
ELSE NULL END

The best way to get to that Days in Order Date Advanced offset is to set the filter to Time Frame = Yesterday, and then click to Advanced. Here's a related post.

Hi Chris,

So I implemented the flag filter for the first widget but it doesn't seem to have changed anything.

Regarding the 2nd widget, where do you find [Days in Order Date Time Frame]? as I am struggling to find it when creating the formula.

With these changes - would this not make it that it would match up the date each year instead of the day in the campaign? Just reading it, it seems like it would match up March 14th 2023 to March 14th 2022 and so on. Not Day 39 of this year's campaign to day 39 of previous years. 

Admittedly our campaign dates each year are all over the place due to when we go on sale changing each year so that's why it has to be done more to the campaign than the actual date.

Thanks for getting back to me and giving me some help. Looking forward to moving further with this!

Hi Chris,

So I implemented the flag filter for the first widget but it doesn't seem to have changed anything.

Regarding the 2nd widget, where do you find [Days in Order Date Time Frame]? as I am struggling to find it when creating the formula.

With these changes - would this not make it that it would match up the date each year instead of the day in the campaign? Just reading it, it seems like it would match up March 14th 2023 to March 14th 2022 and so on. Not Day 39 of this year's campaign to day 39 of previous years. 

Admittedly our campaign dates each year are all over the place due to when we go on sale changing each year so that's why it has to be done more to the campaign than the actual date.

Thanks for getting back to me and giving me some help. Looking forward to moving further with this!

On the first widget, I see that you probably have Season on Categories, and the Fiscal Year to Date Flag may not align with that. Still, would you check that you're using the [PERFORMANCE DATE].[Fiscal Year to Date Flag], as opposed to the one for [ORDER DATE] or any other, and see what that looks like? You could also, just for testing purposes, add it on Categories to that within each Season we see the Yes/No split.

Regarding that second formula, you're right. It will look at the date year over year as opposed to the day of the campaigns.

Even though it's not what you're after, I want to document that I used some shorthand with that [Days in Order Date Time Frame] lol... that's [ORDER DATE].[Date] added to the widget as [Days in Date] (as opposed to [Months in Date] or any other granularity), and as a filter in which you've selected the Time Frame type filter set to Days = Yesterday. I figure this is going to be a lot clearer with a video. Ignore that my results are really sparse. I have a little demo database that doesn't have sales on every day of the year...

To do something similar for Order Days from Campaign Start, which isn't a standard field in Analytics (idea?), might be possible with a formula, but probably only in the later Sisense version that comes with v16 and v15.2.34... and, my local data isn't super supportive of my testing this thoroughly, but I think it'll work. With Season on Rows of a Pivot widget, and a widget filter on [Seat Sold Flag = Y]...

SUM ( [Order ID] ,
  SUM ( [Performance ID] ,
    IF (
      ( MAX ( DDIFF ( NOW ( [Days in Performance Campaign Start Date] ) , [Days in Performance Campaign Start Date] ) ) , [Campaign Fiscal Current Year Offset = 0] , ALL([Season]) , ALL([Order ID]) , ALL([Performance ID]) ) - 1
      = MAX ( DDIFF ( [Days in Order Date] , [Days in Performance Campaign Start Date] ) )
    , [Total Ticket Count] , NULL
    )
  )
)

Within each Season on Rows, look at each performance in each order, and if the count of days between NOW() and the campaign date for that performance (minus 1 to get yesterday) is the same as the count of days between the order date and the campaign date, then return the ticket count for that performance in that order to the overall SUM.

Hi Chris,
Thank you for the video. Really helped me work it out.

Regarding the first widget, there doesn't seem to be an option for Fiscal Year to Date Flag under Performance Detail - could it be another option instead of Order Date?
The only options I have are: Attended Date, Create Date, Lineitem Source Campaign Start Date, Order Date, Order Source Campaign Start Date, Package Campaign Start Date, Performance Campaign Start Date, Performance Date, Performance On Sale Date.

Also, how do you filter it onto the categories specifically instead of the whole widget?

I duplicated the 2nd widget to try it both of your options. The first formula didn't seem to change anything and the 2nd one failed when it tried to process which is annoying. 
Was hoping to at least get the calendar date working as it would be a starting point!

I was having a think though - instead of doing it for the same day last year or the 'campaign start date' - would there be a way to manually input the start date for each year to work from? Would be a different date for each Season. Having a look at the campaign start dates, they don't really work with when we went on sale for the festival and what would work for this report so just trying to look at alternate routes.

Cheers

I meant to say [PERFORMANCE DATE] (edited above) for the Fiscal Year to Date Flag.

And when suggesting adding it in Categories to see how it splits the data before or instead of adding it as a widget filter, I meant clicking +Add on Rows and putting it below Season.

The second widget second formula errors out on 15.2.31 due to a Sisense defect but should work once you're upgraded to the latest v15 or v16.

In response to your "think", did all the events in the season go onsale at the same time, or would you want to consider repurposing the Default Sales Dates Start Date to store that start date for all events in each season? That would then flow into the Order Days Since OnSale field. If you want to go that route I suggesting testing one season first and seeing if the 2nd formula can be modified to support your goal.

SUM ( [Order ID] ,
  SUM ( [Performance ID] ,
    IF (
      ( MAX ( [Order Days Since OnSale] ) , [Season Fiscal Current Year Offset = 0] , ALL([Season]) , ALL([Order ID]) , ALL([Performance ID]) ) - 1
      = MAX ( [Order Days Since OnSale] )
      , [Total Ticket Count] , NULL
    )
  )
)

Last option is that it could be a pretty small customization to produce a field or two in Analytics that give you exactly what you need without all the formulas and fiddling with Default Sales Dates Start Date.

Hi Chris,
Can confirm that all events in the seasons went on sale at the same time. Have checked for every season and even though each season goes on sale at a different time, each event in the individual seasons do.

The first widget has changed now with that filter being added - going to watch it for a couple of days to see how it changes.

Tried the IF function and it wouldn't go through - saying the IF function doesn't accept 5 parameters and the ending of ) wasn't right.
Will update you after the weekend with how the first filter goes.

Hi Chris,

Just updating again. Unfortunately the widget didn't change at all over the weekend, even for the current season.