5 Replies Latest reply on Oct 13, 2016 9:16 AM by paul.wanless

    Displaying active customer percent by date column

    paul.wanless

      So I'm stuck with trying to get a percentage breakdown of active accounts with total accounts created.  Can't show actual customer data so I'll make a rough representation of the data and the pills type and names relative to the sample data.  Date columns are date/time but I didn't get granular in the sample data.  I'm trying to create a report to give up to date active users by the date so Week/Month/Qtr/Year with the running total.  So far, I can only get the count  of active accounts and inactive but no method to get percentages.  I would also like to be able just to get a current active user percent.

       

      COLUMN: D(Year(Create Date[customer table])) D(Qtr(Create Date[customer table])) D(MONTH(Create Date[customer table]))

      ROW: M(CNTD(Customer ID computed using Table(across)))

      COLOR:  D(c. Active Cases)

       

      c. Active Cases

      IF COUNTD([Case ID]) > [p. Active Case Count] THEN "Active"

      ELSE "Inactive"

      END

       

      p. Active Case Count = 1

       

      I have two tables an order table with an order create date

      Table Name: Cases

      CreateDate Customer IDCase ID...
      1/1/2016 00:00:00

      1

      1
      2/1/2016 00:00:0022
      2/14/2016 00:01:0023
      3/1/2016 00:00:0034
      3/1/2016 00:00:0025
      4/1/2016 00:00:0016
      4/1/2016 00:00:0047
      4/1/2016 00:00:0028
      4/1/2016 00:00:0039
      5/1/2016 00:00:00410
      5/1/2016 00:00:00511

       

       

      and a customer table

      Table Name: Customers

      Customer IDCreate Date
      11/1/2016 00:00:00
      22/1/2016 00:00:00
      33/1/2016 00:00:00
      44/1/2016 00:00:00
      55/1/2016 00:00:00
      65/1/2016 00:00:00

       

      In Tableau I have the Customer Table Left Joined to the Case List for the datasource.

       

      So there can be accounts created that have never created a case because they decided to not to create a case.

       

      So this will create records of all the customers at least with the cases but the order create date is null in the query for that customer ID.

       

      To get the dates on the same axis, I also create a new calculation to try and get them to combine better

      D(Create Date Merged)

      ```IF ISNULL([CreateDate (Cases)]) THEN [Create Date] ELSE [CreateDate (Cases)] END```

       

      Now I've been able to get the data get a running total of the accounts.

       

      ```RUNNING_SUM(ZN(COUNTD([Customer ID])))```

       

      But when I try to split the data to get a ratio to get the percentages, then that's where it breaks down for me.

       

      I've tried using the lookup function to get the customer ID to "carry over" so when looking at the date I could get results such this so it's possible to sum the data to visualize it but again I'm stuck as I cannot get it to this state.  0 being inactive and 1 being active in status.

       

      Create Date MergedCustomer IDStatus
      Jan 201611
      Feb 201621
      Feb 201610
      Mar 201621
      Mar 201631
      Mar 201610
      Apr 201611
      Apr 201621
      Apr 201631
      Apr 201641
      Jun 201610
      Jun 201620
      Jun 201630
      Jun 201641
      Jun 201651
      Jun 201660

       

       

      I've looked at all the domain padding, domain completion, and other related kb by Joe Mako and Jonathan Drummey but it doesn't seem to get me there with dimensional only data.  The examples only seem to be given with with sales numbers where the data is already in a measure form and where the data has not been aggregated to create a measure to determine the status of the account at that date column.

       

      Message was edited by: Paul Wanless I added a sample workbook of the data to look at with some of the key calculations that had been tried out to try and get it to work along with some views. 

        • 1. Re: Displaying active customer percent by date column
          Shinichiro Murakami

          Paul,

           

          It's quite difficult to try on this one without having actual data.

          Could you please attach sample data by excel or tableau packaged workbook?

          Don't need actual data, but we can gain time with using sample data.

           

          thanks,

          Shin

          • 2. Re: Displaying active customer percent by date column
            paul.wanless

            Hi Shinichiro,

             

            I added the tableau package to try out with the relevant data to see if you can find what I'm missing...

            • 3. Re: Displaying active customer percent by date column
              Shinichiro Murakami

              Paul,

               

              I hope this helps.

              Create three calculate fields (You can consolidate these into one, I just used three for step by step explanation)

               

              [SM_Active User]

              RUNNING_SUM(COUNTD(if [c. Active Cases]="Active" then [Customer ID] end))

               

              [SM_Total]

              RUNNING_SUM(COUNTD(if [c. Active Cases]="Active" then [Customer ID] end))

              +

              RUNNING_SUM(COUNTD(if [c. Active Cases]="Inactive" then [Customer ID] end))

               

              [SM_Active User%]

              [SM_Active User]/[SM_Total]

               

              Thanks,

              Shin

              1 of 1 people found this helpful
              • 4. Re: Displaying active customer percent by date column
                Yuriy Fal

                Hi guys,

                 

                The main problem to be addressed here is

                how to Bin each [Customer ID] along Dates --

                from the time of his Creation to the point in time

                where # Active Cases condition is met -- and beyond.

                 

                The very first event for each Customer is his Creation

                (given by the [Create Date] of the Customer table).

                From that point in time a Customer is 'Inactive' --

                until she gets enough Cases to met an 'Active' condition.

                 

                The JOIN'ed datasource (as in the original demo_book.twbx)

                is not a good fit for the above. All events (rows) --

                both Customer Creation and Cases -- should have

                a common Date dimension (besides Customer ID itself),

                so a UNION of both Customers and Cases would be better here.

                 

                The above logic implies using RUNNING Table Calculation(s),

                so any pure Aggregate / LOD solution couldn't help either.

                 

                Last but not least, having 'Active/Inactive' Bins as a Table Calculation

                left no other choice than using another Table Calc(s) to get Bin Size.

                I've been using RANK functions (following a recipe by Joe Mako).

                 

                Please find the attached.

                Hope it could help.

                 

                Yours,

                Yuri

                2 of 2 people found this helpful
                • 5. Re: Displaying active customer percent by date column
                  paul.wanless

                  This looks like it would work as a workaround for now.  As Yuriy pointed out, I was trying to get the percentages in the bars themselves to show user base growth but see how many are active to a certain level.   I think I'll have to try a different way to get the data though in a way that can do this.  It just seems like it should be possible since the data is stacked with the values needed to just get that percentage.