3 Replies Latest reply on Jul 10, 2013 11:55 AM by Jim Wahl

    How to use "total of customers involved" in a calculation for a chart that may not show them all

    adam.medcalf

      Hi,

       

      My second question of the day, that may or may not involve table calcuations!

       

      My scenario is that I have a set of customers that do business with us regularly. Some of these we perform an intervention on at a set date, and I want to check the effects of how often they transact with us after the intervention and after how long. For instance customer 1 might transact with us once 1 month after our intervention, customer 2 might transact with us on month 1, 3 and 5, and customer 3 might never transact with us again. I would like to display the proportion of the customers we performed the action on that transact with us each month (i.e. propensity to transact over time).

       

      A simplified version of the data might look like this:

       

      Customer IDTransaction IDTransaction dateIntervention performedMonths since intervention
      123456999991/1/2013YES3
      123457998781/1/2013NO
      123456789782/2/2013YES4

       

      I have no problem getting a count of the customers who transact by month from the original population by counting the sale IDs with Months since intervention on the X axis - but I would like to change this into a percent of the total customers intervened on, similar to the below example.

      Capture.GIF.gif

       

       

      What I therefore need to do is, for each “month since intervention”, divide the count of transactions by the total number of distinct people where “intervention performed” = yes. However because some people may never transact with us again and hence not appear in the resulting line chart at all, I can’t find a (easy) table calculation that will do this.

       

      The data behind the analysis is updating each day (from a Powerpivot) and visualised using several different filters so I would rather not have it a manually input fixed value, if that is even possible. I was trying to consider ways to access results of a difference calculation in this chart but couldn’t figure it out. Again, any help very much appreciated.

       

      Thanks for any help!

        • 1. Re: How to use "total of customers involved" in a calculation for a chart that may not show them all
          Jim Wahl

          Hi Adam,

           

          However because some people may never transact with us again and hence not appear in the resulting line chart at all, I can’t find a (easy) table calculation that will do this.

           

          If I understand your goal correctly, it seems like this is the key problem: You can't find the the total number of interventions from the transaction data alone. ...

           

          However, you can find the total number of distinct people who transacted and where "intervention performed" = yes with with something like,

          COUNTD(

              IF [Intervention performed] == "YES"

              THEN [Customer ID]

              ELSE NULL

              END

          )

           

          You must have another table that contains customer ID and intervention date? You could add this as a data source and use data blending on date to combined these into one view.

           

          Jim

          1 of 1 people found this helpful
          • 2. Re: Re: How to use "total of customers involved" in a calculation for a chart that may not show them all
            adam.medcalf

            Hi Jim,

             

            Thanks very much for your reply, sorry it took me a while to come back.

             

            You identified precisely the main problem, and your solution makes sense.

             

            Unfortunately due to the (strange?) way our data is structured, the intervention dates are in the same table. They are triggered as a result of a previous transaction, so in reality I rather over simplified the example above, my apologies. It would look more like:

             

            Customer IDTransaction IDTransaction dateIntervention performed in pastMonths since last interventionNew intervention triggeredIntervention date
            12345611/1/2013YES20/1/2013
            123456220/2/2013YES1NOn/a

             

            Anyway, you inspired me to duplicate the datasource, thinking that your idea would work if I did it on the secondary datasource. However I'm finding that Tableau won't let me use (even drag and drop) fields from the duplicate datasource into the analysis I'm doing using the original data source. The field list is entirely grayed out and has an orange line up the side of it. Do you know if that is normal, or am I doing something wrong?

             

            Capture.GIF.gif

             

            My data source is a Powerpivot, which is a type of cube, so I won't if it's anything to do with Tableau's limits on OLAP data sources.It's not mentioned at http://kb.tableausoftware.com/articles/knowledgebase/functional-differences-olap-relational so perhaps I am just missing a trick. Any experience with duplicate datasources?!

             

            Thanks again for your information so far.

             

            Adam

            • 3. Re: How to use "total of customers involved" in a calculation for a chart that may not show them all
              Jim Wahl

              Hi Adam,

               

              Glad to have helped with inspiration . I apologize for not seeing the reference to PowerPivot.

               

              I'm still a little confused as to whether you have rows for all customers who had an intervention, regardless of whether there was a subsequent transaction / transaction ID? This is the count you need for the denominator, total customers with intervention.

               

              I really don't have much experience with OLAP, but my initial thought is that this count needs to be generated in PowerPivot or via an MDX query from Tableau.

               

              If you were using a relational database and if you had rows in your table for all customers who were interviened, you'd do something like

              TOTAL(

              COUNTD(

                  IF [New Intervention triggered] == "YES"

                  THEN [Customer ID]

                  ELSE NULL

                  END

              )

              )

               

              Total allows you to aggregate at a different level (all dates) than the partitions in the view. A duplicate data source shouldn't be required.

               

              But again, I'm not sure whether you can do this with an OLAP data source. Maybe you want to repost your question with OLAP in the title and someone may pick it up with experience with OLAP.

               

              Sorry I couldn't be of more help.

               

              Jim