In the Tickets Cube: Order [Time] Prior to Performance is so valuable to our organization. Visualizing this data can be problematic though, such as "Order Weeks Prior to Performance", because it visualizes the current, incomplete week of sales, and indicates a drop-off that isn't "real." It's further problematic if I want to visualize a trend line, or perform any other calculation, because that incomplete data-point skews the results.
Is there a way to cull the data so that it only pulls COMPLETED weeks prior to a performance?
Hi Daniel,
This is an interesting question. We should be able to say that the first (MIN) day of the of the current Week Prior to Performance is less than 7 days prior to today. So we could compose a formula that returns values any given Week Prior to Performance only when the minimum date within that week is 7 or more days prior to today.
The Days Difference function (DDIFF) takes two [Days in Date] fields and returns the count of days between them. We'll use Order Date for one of these date fields.
The Now function takes a [Days in Date] field and returns today's date, so we'll use that for one of the two DDIFF dates.
If the count of days between an order date and today is 7 or more, then it has to be in a Week Prior to Performance that is before the current Week Prior to Performance. If that criteria is true for a given week, then we'll use an RSUM( [Total Ticket Count] ), and if it's not true, we'll return a NULL. It looks like this:
CASE WHEN MAX( DDIFF( NOW( [Days in Date] ) , [Days in Date] ) ) < 7 THEN NULLELSE RSUM( [Total Ticket Count] ) END
And can been seen as the output of the Formula1 value here:
Brilliant. Thank you!