Hello!
I am a Tessitura analytics newbie. I am trying to create a sales curve graph with the number of tickets sold in the last two weeks leading up to an event. When I filter for 14 days prior to the event, the ticket count starts at 0. Is it possible to have the ticket count begin at the amount of tickets which were previously sold? In the example below, the second graph should start at 178 tickets instead of 0.
Thanks so much!
Here is a graph of all sales:
Here is a graph of the last two weeks leading up to the event.:
Hi Aaron,
It looks like your data includes 1/1/2000 as one of your dates which is why the graph starts at 0. Could you post your filters? That will help us figure out what's going on there!
Thank you!
Here is a picture of the filters:
Try something like this
You can then break by production season if you want more details.
You can add a filter on [Seat Sold Flag] = Y to eliminate those 1/1/1900 results for unsold seats.
To get the -14 day to include all prior sales, use a case statement such as this...
RSUM ( CASE WHEN MAX( [Order Days Prior to Performance] ) = -14 THEN ( [Total Ticket Count] , [Order Days Prior to Performance <= -14] ) WHEN MAX( [Order Days Prior to Performance] ) = 0 THEN ( [Total Ticket Count] , [Order Days Prior to Performance >= 0] ) ELSE [Total Ticket Count] END)
In the results for Day = -14 this overrides the Order Days filter and includes all days before or on 14 days Prior to Performance. Just in case there are any orders after the Performance Date, the results for Day = 0 include the Order Days 0 or later. Otherwise it just includes the ticket count for the given day prior to performance.
Depending on what Aaron wants to show, if you wanted a running sum then this formula would work.Notice that the first value starts at 1237 even though the ticket count was 44 for that day. The RSUM of the ticket count before that last 2 weeks was 1193. 1193 + 44 = 1237.If that is not what you wanted, then please ignore. The drawback to this method is I had to hardcode the Order Date filter within the formula.
I think this looks right for what I am trying to do. How do you hardcode the order date filter?
The concept is called a Filtered Value. You add a filter on the formula level.Syntax:
( [Aggregation], [Filter1], [FilterN] )
...And then I added an Order Date filter on the Widget level for >= 8/14/2022 (in my specific example).The Formula filter allows you to grab the numbers from Before the filter on the Widget level and then add them together.
I added this formula in but the X axis is now showing from the first sale leading up to the performance. I am hoping to only show the final 2 weeks leading up to a performance. Order days up to the performance on the X axis also didn't work. Here is what the graph looks like.
Aaron Rivkin, do you have the field [Order Days Prior to Performance]? It looks like I'm doing the same type of dashboard tile that you are and I'm not getting your same issue. But, I'm using a filter field that is slightly different, and maybe you and I are not using the same cube. Here is what I have: