1 Reply Latest reply on Jun 30, 2016 1:45 PM by derek.dupont

    Data Blending and Level of Detail Calculations

    derek.dupont

      Hi all,

       

      I'm relatively new to the data blend option in Tableau and I'm not sure if what I'm trying to currently accomplish is possible. I've attached a generalized version of the project as a packaged workbook.

       

      In this project, I have one data source (ExampleCustomerList) which is a list of customer-site pairings (a single customer can have many sites) and a purchase date for each one. I then have a second data source (ExampleEvents), which contains a list of unique event Ids per customer-site pairings, as well as start and end times for those events. I use the customer and site dimensions to establish a relationship between the two data sources. Both these data sources are represented as excel workbooks here, but in my actual project I am working with a Tableau Server connection (for a regularly refreshed Salesforce extract) and a CSV file.

       

      I have two parameters which are used to set the period of time I want to analyze (Period End Date and Period Length).

       

      I then create two calculated fields:

       

      1) Event Duration within Period - This is the length of an event during the specified period length. If the event finishes within the period, then it's simply the difference between the start and end time. Otherwise, the period end time is substituted for the end time.

       

      2) Inactive Time % - This is where I make use of the blended data sources. If a site's purchase date is after the period end date, then I return null; if the purchase date is before the period start date, then this is the percentage of time during the period that there was no event occurring; if the purchase date is during the period, then I only use the fraction of time after the purchase date and before the period end. Basically, I'm looking at how much inactive time a specific site actually had.

       

      This works fine and dandy if I just want to display these calculations per site:

      But, I'd like to take things a step further and calculate the % of sites with an inactive % above a given threshold. Normally, I would utilize a level of detail calculation, something like the following:

       

      SUM(IF {INCLUDE [Site]: [Inactive Time %]} > .80 THEN 1 ELSE 0 END)

       

      However, this will throw the error "All fields in a level of detail expression must come from the same datasource" (specifically underlining the "Inactive Time %" field). I would love if these data sources were centralized, but that is currently out of my control. Am I using data blending correctly here? I was working under the assumption that this was essentially a JOIN operation where I could tack the "Purchase Date" from the ExampleCustomerList source onto the respective events in the ExampleEvents source.