This is a complex problem that has taken weeks to figure out. Here is a packaged workbook (version 2018.1) to illustrate the issue (since the data is sensitive I have anonymized it by just using the id's.)
Aim: To limit records by 2 different date filters, as shown below. Note how when the billing date surpasses the acquisition date, no more subs/users can be acquired and therefore they can't pay, but we can still receive revenue from existing subs/users (highlighted with red zero's.)
Context: This table shows the ARPS (ave revenue per subscriber/user) and ARPPS (ave revenue per paying subscriber/user) on an Aggregator, Partner, and Club, and Acquisition Date Level.
- Gross Revenue
- Net Revenue
- Acquired Subs
- Running Total of Acquired Subs
- Gross ARPS (=Gross Revenue/Running Total of Acquired Subs)
- Net ARPS (=Net Revenue/Running Total of Acquired Subs)
- Paying Users
- Running Total of Paying Users
- Gross ARPS (=Gross Revenue/Running Total of Paying Users)
- Net ARPS (=Net Revenue/Running Total of Paying Users)
Important calculated fields to take note of:
- ADate Range: [Billing Date] >= [Start Date] and [Billing Date]<= [Billing End Date]
- BDate Range: [Acquisition Date] >= [Start Date] and [Acquisition Date] <= [Acquisition End Date]
- For any selection: Acquisition End Date must <= Billing End Date
- Currently it limits the visualization to the 'shortest' range selected, ie therefore filters by Start Date ---> Acquisition End Date which automatically means that billing end date cuts off at Acquisition End Date as well, even if it's range is longer
- There are 2 excel spreadsheets "ARPS" and "ARPS by Acquisition Date".
- They are blended on Acquisition Date, Aggregator id, Club id, mno id, partner id, rate.
- One acquisition date in "ARPS by Acquisition Date" has multiple associated billing dates in "ARPS" (as once a sub is acquired, in is periodically billed later for the foreseeable future)
Extra Info: Acquisition and Billing Dates are entered in the incorrect number format into Tableau, it's supposed to be "YYYY/MM/DD", yet it comes out as "YYYY/DD/MM". This can't be simply formatted to be switched, but I read up it has to do with my location settings (changed my language to English (UK) but it didn't help.) Regardless, you can still see the general idea even though it increments by months instead of days.
Any help would be GREATLY appreciated.
APRS Q.twbx 26.1 MB