I've been asked a tricky question by my CFO. Before I solve it in the Database I want to try filtering in Analytics
I have about $19k of ticketing fees in 2019. Do you know what value of these relate to 2020 sales?
We use 2019 Fees from January to December 2019 and flip over to the new year in 1-Jan-2020. Because we start selling 2020 shows in September 2019 there is a number of 2019 Fees that were accumulated in orders for the 2020 season.
Now this is of course a fuzzy question because order can contain all kinds of things 2019 and 2020 both but what I've like to do is only return Fee Value where the Order ID contains a particular 2020 Production Seasons
I'm scratching my head about the Analytics formula. In SQL I write a select where Order ID contains - search for the Order IDs with a 2020 Prod Season No.
Any thoughts?
H
Hi Heath,
Are the fees sold in 2019 associated with a 2020 Season? If so, the Season Fiscal Year field in Analytics Finance cube applies to both events and fees and may split them out for you.
Hi Chris,
Unfortunately not. Reason being is that we don't want to trigger 2019 and 2020 fees (essentially double charging fees) to people who have tickets to 2019 and 2020 perfs both in the cart.
Ah yes,
You can filter on Order ID in the widget, using a Ranking type filter set to return the TOP 100000 from a filtered value formula for the (Total Ticket Paid Amount , Production Season filtered to those you wish ). Because it's a filtered value, it'll in essence return NULL for Order ID that don't have any Ticket Paid Amount in the selected Production Seasons.
Unfiltered order counts and fee amounts from 2019:
Filtered:
Oh of course! Thanks Mister ... I was just writing the SQL as you posted this.
Gold!