5 Replies Latest reply on Jun 14, 2018 10:32 AM by Michel Caissie

    Limit measures by 2 Varying Date Ranges

    Steffi B

      Hi All

       

      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.)

      Screenshot (188).png

       

      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.

      Columns:

      • 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

       

      Data Sources

      • 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.

        • 1. Re: Limit measures by 2 Varying Date Ranges
          Michel Caissie

          Steffi,

           

          Regarding your date range filtering problem,

          if you put two boolean on the filter shelf and set the value at True,  Tableau will do  if ADate Range = true  AND Bdate = true.

          What you can do is replace those 2 filters with the following  calculation;

          ATTR( [ADate Range] ) OR ATTR([ARPS ].[Bdate Range])

           

          By keeping the true values you will get the data from both range.

           

          Regarding the date format problem, if you look in  ARPS,  Billing Date  seems to be correctly formatted with a default  MM/DD/YYYY (sheet 5). It's easy to see because you have days higher than 12.  If you are saying that  Acquisition Date (sheet 4)  is default formatted  DD/MM/YYYY instead of  MM/DD/YYYY, then I would check in your Excel file , if  both columns have the same Date format.

           

          Michel

          • 2. Re: Limit measures by 2 Varying Date Ranges
            Steffi B

            Thanks for taking the time to both read through and attempt my complicated question.

             

            Unfortunately this isn't the solution I was looking for - filtering by ATTR( [ADate Range] ) OR ATTR([ARPS ].[Bdate Range]) still limits the data to the shortest range between the two.

            I require the 'longest' range, and if the billing end date > acquisition end date, then the revenue from acquired subs/paying users mustn't be added to the revenue.

            • 3. Re: Limit measures by 2 Varying Date Ranges
              Tim Dines

              Can you export your file in 10.5.x format?

              • 4. Re: Limit measures by 2 Varying Date Ranges
                Steffi B

                Here's the 10.5 Packaged Workbook.

                • 5. Re: Limit measures by 2 Varying Date Ranges
                  Michel Caissie

                  Steffi,

                   

                  If you look on sheet 2, you see that when you use  ARPS by Acquisition Date as the Primary Source,  the Billing Date returns a *  because there is more than one Billing Date  for  each Acquisition Date.  You can see on sheet 3, that when you use  ARPS as the Primary Source, you see all the Billing Dates.

                   

                  Data blending is not like an inner join. For every Acquisition  Date from the Primary Source, Tableau tries to make an aggregation of the Billing Dates from the Secondary Source. And since Billing Date is a dimension, it tries to aggregate using ATTR(), which return * (undefined)  because there is not a unique value of Billing Date for every Acquisition Date.

                   

                  Since all values of AggregatorId, ClubId and PartnerId  seems to be present in both datasource, I suggest that you build your report using ARPS as the Primary Source. It should work since for each Billing Date there is a unique  Acquisition Date.

                   

                  Michel