Hi All,I am looking at the sales information for sales yesterday, specifically at the income from tickets sold below the base price (on a discount). The pivot I have set up has the line items are correctly calculating, however the the grand totals are not following the formula. When I remove either of the two variables mentioned in the formula from the rows section of the pivot, the results are the same as the grand totals, and unfair feels incorrect.
I am fairly new with tessitura, and was wondering if anyone might have any insight?
Hi Bryn,What is likely happening is the grand totals are also referencing your formulas. If you click on the three dots next to your value, you can find other options for "Subtotal By." Try selecting "Sum" rather than the "Auto" default and see if that does the trick!Happy analyzing!Christine
I think Christine is correct. The issue is that "Total Ticket Paid Value" is really SUM([Ticket Paid Value]). This means you are attempting to SUM an already SUM'd value. Whenever you see "Total" associated with a value, it means that it is really using the SUM function for the value. In other words, [Total Ticket Paid Value] = SUM([Ticket Paid Value]).
Hi All, I have adjusted this based on Christine's advice, and it has corrected the grand total. Thank you Christine!However, I am banging my head against a strange problem!When I switch between having the unfiltered zone field enabled and disabled, the full price and discount grand totals change. Since none of these formulas reference the zone, I am unclear why these totals would change?I have looked down to an individual ticket ID level, and that has different grand totals as well (and presumably the most accurate).Thank you all so much for your help and brains.
Hi Bryn,Looking at your formulas, the way they are calculating is going to reference what is in your rows, whether it's Zone or Production, regardless of that being part of the formula. In other words, it's taking the total paid value for that row and comparing the total seat base value for that row.This is a next-level analytics concept, but I believe you may want to try "multi-pass aggregation" which is a way to have your formula calculate - for each ticket with that zone, look at the total value vs. the total base value.
So the variation on your formula would just add to the beginning:SUM([Ticket ID], IF( [Total Ticket Paid Value] = [Total Seat Order Date Base Value] , ( ( [Total Ticket Paid Value] ) ), 0))
SUM([Ticket ID], IF( [Total Ticket Paid Value] = [Total Seat Order Date Base Value] , ( ( [Total Ticket Paid Value] ) ), 0))
What we're adding is to say, for each ticket ID, if the paid value and base value is the same, now add it up.
Give that a try and see if it works! This video workshop will explain more about multi-pass aggregation: community.tessitura.com/.../57795
Happy analyzing,Christine
If there’s a chance that you might be referencing unprinted reservations, then using SLI ID instead of Ticket ID will work for that. Anyunprinted reservations will all fall under the same Ticket ID = -999999.
Thank you so so much Christine Wingenfeld, I am going to work on this today! I appreciate the thoughtful and complete response!Hopefully this is a solid use-case for expanding my knowledge and I'll be on here in a few months helping others!