Pivot Table: Calculating Differences

Hi Rosanna,

Let's ditch the DIFFPASTYEAR() function for this use case. The date-related Functions that are available in Tessitura Analytics all require a Date data type field to work (the fields with the little calendar icon next to them in the Data Browser). For any date dimension (collection of fields related to a given date) in Tessitura Analytics there are many fields we can choose from, allowing us to look at that date from many different angles and groupings. Week of Year is one example, but that's just a raw integer value in Analytics and not a Date data type that the date-related functions can work with.

Likewise, those date functions, when grouped by Production Season as you have on Columns, would look for sales from the past year within the same Production Season grouping, and wouldn't be able to break out of any given Production Season to determine the sales figures from the prior Production Season.

So to accomplish what it looks like you're after probably requires creating a formula using a conditional statement that determines is aware of Production Season is currently in scope. Or said another way, a formula that knows which Production Season the value is currently grouped within. Then based on that awareness, return the sales from the prior Production Season. One way to accomplish this might be to check whether the MAX Season Fiscal Year for any given Week of Production Season, is the Fiscal Year for a specific Production Season.

We can use a condiditonal CASE statement to list many conditions to check in our formula, where each condition can use a different formula output. The result returned by the statement will be from the first condition in the list that is met. For your example, let's use a conditional check whether the MAX Season Fiscal Year = 2021, and if it does, return the [Total Ticket Count] from the Production Season, 1920 Broadway Morsani.

CASE
WHEN [Max Season Fiscal Year] = 2021
THEN ( [Total Ticket Count] , [Production Season] )

The THEN formula is a filtered value where the [Production Season] is set to 1920 Broadway Morsani. Because Formula filters override Widget and Dashboard filters, this filtered value will break out of the Production Season grouping on Columns and return the 1920 Broadway Morsani value to the 2021 Broadway Morsani grouping. Repeating this WHEN/THEN combination, and adjusting the Fiscal Year and selected Production Season for each, results in a full formula that looks something like this:

CASE
WHEN [Max Season Fiscal Year] = 2021
THEN ( [Total Ticket Count] , [Production Season1] )
WHEN [Max Season Fiscal Year] = 1920
THEN ( [Total Ticket Count] , [Production Season2] )
WHEN [Max Season Fiscal Year] = 1819
THEN ( [Total Ticket Count] , [Production Season3] )
ELSE NULL END

The ELSE bit at the end is what the formula should return when none of the prior conditions is met. In this case I chose NULL or a blank value, rather than a 0. Finally, a CASE statement has to be closed with the END keyword.

At this point we're close, but are only returning the Total Ticket Count from the prior Production Season, and not the difference. To do that, just add [Total Ticket Count] to the beginning of the formula, and subtract the CASE statement.

Thank you so much Chris!! This was extremely helpful 

Chris, do you know why I am receiving this error? I am attaching a screenshot of the CASE formula you provided and the error I am receiving. 

Shouldn't the FY's be equal to 2021, 2020, and 2019? I'm guessing that you don't have FY's from the 1800's in your database.

Hi Rosanna,

Neil is likely correct for the TR_SEASON.fyear. I am sorry I don't know what [Years (FY) in Week 1 Begin Date] is or how to help with that field, as that's custom data in your Analytics.

Chris

Hi Chris. Yes, I realized my mistake after asking the question. Still, the formula is returning only zeros. Do you know where I am mistaken? 

I am filtering season to the corresponding season of each year. Is that correct? 

Best, 

Rosanna

Hi Rosanna,

Would you try adding Season Fiscal Year above Season in Columns? Let's validate that the Season values you expect are what's in the data.

Of course! Something like this: 

Here's a version for you to test on the standard Seats and Tickets data source.

PastSeasonDifference.dash

Change the Season Type on the dashboard, and then edit the formula as follows...

Edit the Variance Prior Season Ticket Count value and click on each Season filter, selecting Edit Filter. Then select the Season prior to the listed Max Season Fiscal Year... for Max Season Fiscal Year = 2018, select the 2017 Broadway Morsani Season.

You should end up with something like this:

When you get up to the latest service pack (15.1.7 or later), you can create a version of this that will survive over seasons, without having to edit the selected seasons and fiscal years in the formula and filters.

PastSeasonDifferenceAuto.dash

For this one, just set the Season Type (assuming there's a Broadway Morsani season type). The Season Fiscal Current Year Offset will limit the results to the current and prior 2 seasons.

The Variance Prior Season Ticket Count doesn't reference any specific Season or Season Fiscal Year and therefore doesn't need any seasonal changes.

And the results are the same as above:

One key observation about both solutions... when a season has no tickets sold in a given week, the results in that week are NULL. We can't render or produce a 0 for that given season and week. As a result, the Variance is also NULL even if the prior season did have sales in that week.

Thank you so much for your help Chris! I'll work on it