Analytics formula help

Hi Heath,

I had to alter the holds filter to just include all seats for my data but, here's what I ended up with.

IF (
SUM ( [Ticket Paid/Reserved Count] ) > 0 ,
( [Total Seat Count] , [Saleable Holds] ) * ( SUM ( [Ticket Paid/Reserved Value] ) / ( SUM ( [Ticket Paid/Reserved Count] ) ) ) ,
( [Total Seat Current Base Value] , [Saleable Holds] ) 
)

Your formula worked fine for me, after removing the "double aggregates". Note that you've got SUM(Total<whatever>]) in most places, and [Max Ticket Value] at the end. The formula interface is "helpful" in allowing you to specify aggregates in 2-ways:

1. When selecting the field from the Data Browser, you can accept the default aggregate, or click More... and pick a different aggregate. This will display as [Agg Field Name].
2. In the formula editor, you can directly type an aggregate function, or pull one from the Functions list, which will display as AGG(). Then add the desired field from the Data Browser, but you need to click the More... button and select All Items rather than selecting an aggregation.

For example, instead of SUM ( [Ticket Paid Amount] ) we should see either SUM ( [Paid Amount] ) or simply [Ticket Paid Amount].

From there, some other recommendations, without knowing the whole of your goals.

• The conditional statement on the IF is asking, "has there been at least one paid/reserved (non-comp) ticket on this performance?" We can simplify that to just look at whether the paid/reserved count or amount is greater than 0, rather than doing the whole average ticket price calculation.
• The outcome when the IF is TRUE is to return the value of all seats at an average ticket price of all currently paid/reserved seats. You're getting at this with the paid amount on all tickets divided by the count of non-$0 tickets. That should be fine, especially if people are editing prices and the value of a price type is not what people are necessarily paying for them. If that's not the case, it's simpler/clearer perhaps to look at the paid/reserved value divided by the paid/reserved count.
• Then the FALSE part was multiplying all seats by the greatest value of any sold ticket, which should be $0 if the original condition is false and no tickets have sold. So I moved the seat count multiplier into the TRUE part of the IF statement and made the FALSE part the sum of the current base value of seats.

Best,
Chris

Hi Heath,

I had to alter the holds filter to just include all seats for my data but, here's what I ended up with.

IF (
SUM ( [Ticket Paid/Reserved Count] ) > 0 ,
( [Total Seat Count] , [Saleable Holds] ) * ( SUM ( [Ticket Paid/Reserved Value] ) / ( SUM ( [Ticket Paid/Reserved Count] ) ) ) ,
( [Total Seat Current Base Value] , [Saleable Holds] ) 
)

Your formula worked fine for me, after removing the "double aggregates". Note that you've got SUM(Total<whatever>]) in most places, and [Max Ticket Value] at the end. The formula interface is "helpful" in allowing you to specify aggregates in 2-ways:

1. When selecting the field from the Data Browser, you can accept the default aggregate, or click More... and pick a different aggregate. This will display as [Agg Field Name].
2. In the formula editor, you can directly type an aggregate function, or pull one from the Functions list, which will display as AGG(). Then add the desired field from the Data Browser, but you need to click the More... button and select All Items rather than selecting an aggregation.

For example, instead of SUM ( [Ticket Paid Amount] ) we should see either SUM ( [Paid Amount] ) or simply [Ticket Paid Amount].

From there, some other recommendations, without knowing the whole of your goals.

• The conditional statement on the IF is asking, "has there been at least one paid/reserved (non-comp) ticket on this performance?" We can simplify that to just look at whether the paid/reserved count or amount is greater than 0, rather than doing the whole average ticket price calculation.
• The outcome when the IF is TRUE is to return the value of all seats at an average ticket price of all currently paid/reserved seats. You're getting at this with the paid amount on all tickets divided by the count of non-$0 tickets. That should be fine, especially if people are editing prices and the value of a price type is not what people are necessarily paying for them. If that's not the case, it's simpler/clearer perhaps to look at the paid/reserved value divided by the paid/reserved count.
• Then the FALSE part was multiplying all seats by the greatest value of any sold ticket, which should be $0 if the original condition is false and no tickets have sold. So I moved the seat count multiplier into the TRUE part of the IF statement and made the FALSE part the sum of the current base value of seats.

Best,
Chris

Hi Chris, 

This has been on my mind for a while as I've tried to make this board more accurate. 

The issue I keep coming up against is that Hold Codes seem to be only updated in Full load not Incremental load.  Now that we are in season I'm getting some interesting negative sales values due to sales amounts which have updated incrementally (over night) and the hold codes which are still using values from up to 6 days ago.  

Would you know where I would find a list of which is updated in which load to allay my curious mind.

Cheers,

H