Hello all!
Background: For some of our shows that we had to cancel, we offered our subscribers the option to change their ticket into a "voucher ticket", that they could use and exchange it for a real show once we have programming again. On our cancellation tracking dashboard, I made a widget that shows the total dollar amount currently purchased for the "voucher" performance, so that we could compare dollar amounts for people who chose that option vs a refund vs a gift certificate.
What I'm currently trying to solve is this: Once people start exchanging their voucher tickets for real performances, that number will decrease because the money is no longer on that performance correct? But I'm guessing that in the future we will still want to see the total amount that went towards vouchers, just like we can see the total amount that went towards refunds or gift certificates at the time. We still have a bunch of tickets that need to be converted over to vouchers, but we're also starting to put out some virtual programming that patrons could use their vouchers for if they want. So what that means is that I won't be able to neatly use transaction date as a filter and just set the cutoff to be today, because that will still be missing all the vouchers we need to issue.
What I was thinking I would do would be to create a formula to sum the ticket paid amount where the transaction type is ticket purchase, ticket refund, and ticket change where the ticket change amount is greater than 0. Transactions that have a ticket change amount < 0 would be exchanges out of the performance, so I don't want to include those. But I can't seem to get the formula right to accomplish this. I tried having a value with the formula: (sum([Ticket Paid Amount]) , [Transaction Type] ) where I have a value filter on Transaction type to be Ticket Change, and a filter on Ticket Paid amount > 0, but it just returns all ticket change amounts instead of only those less than 0.
I also can't just use SUM(ticket purchase amount), because most of the tickets came from an exchange to begin with, so the majority of my transactions for this performance have the type "ticket change". Right now I've just been trying to solve this in the Finance cube, but open to others if there's something I'm missing.
Does anyone know if what I'm trying to do is possible? Any advice?
Thanks!
Sara
Hi Sara,
Try: ( sum([Ticket Paid Amount]) , [Transaction Type] , [Ticket Paid Amount] )
Make sure that first occurrence of Ticket Paid Amount is of type "All Items" and not filtered. Then use the second occurrence of Ticket Paid Amount as the filter for > 0.
Ah that looks like it should do it! I had tried that originally, but it threw me off when I was looking at a past show as an example, and if someone exchanged within the production season multiple times it made the amounts higher than what it should be. But I think that will be fine in this case, since there's no reason for someone to exchange a voucher ticket into another voucher ticket.