2 Replies Latest reply on Jan 25, 2017 11:29 AM by Ivan Rankenburg

    Counting transitions from one state to another

    Ivan Rankenburg
    Ivan Rankenburg Jan 25, 2017 10:11 AM

      Hello, I need some Tableau help.

       

      My data is structured as follows:

      Every day, is a user spends money, there is a record generated classifying them as a certain level of spender.  Spending classes are A, B, and C. (A spends the most, C spends the least).  So my data might look like:

      DateUseridSpend level
      1/1/20171A
      1/1/20172C
      1/1/20173B
      1/2/20173B
      1/2/20174B
      1/2/20175B
      1/3/20171B
      1/3/20172C
      1/3/20174A
      1/4/20172A
      1/4/20173C
      1/4/20175B
      1/5/20171B
      1/5/20173C
      1/5/20175A

      Notice that user's don't necessarily spend money every day...

       

       

      I need to count transitions...  For example, I need to compute this:

      DateTransitionsComment
      1/1/2017
      .
      		To ATo BTo C
      From A000
      From B000
      From C000
      From never seen before111
      		3 new user's appeared, with no prior data
      1/2/2017
      .
      		To ATo BTo C
      From A000
      From B010
      From C000
      From never seen before020
      		1 user who was previous B (the day before) remained B.  2 new users appeared and where class 'B'
      1/3/2017
      .
      		To ATo BTo C
      From A010
      From B100
      From C001
      From never seen before000

      User 1 was class A 2 days prior, and now is class B.

      User 2 was class C 2 days prior, and is still class C.

      User 4 was class B the day before, and now is class A.

      1/4/2017
      .
      		To ATo BTo C
      From A000
      From B011
      From C100
      From never seen before000

      User 2 was A the day before, and now is class B.

      User 3 was class B 2 days before, and now is class C.

      User 5 was class B 2 days prior, and is still class B.

      1/5/2017
      .
      		To ATo BTo C
      From A000
      From B010
      From C001
      From never seen before100

      User 1 was class B 2 days prior, and is still class B.

      User 3 was class C the day before, and is still class C.

      User 6 was never seen before, but is now class A.

       

       

      The hard part is, the previous record for a given user may be many days prior. 

      Ultimately, I just need to count transitions.  Any help?  I feel like I've tried everything.  I am starting to suspect Tableau just cannot do this.

      • 119 Views
      • Tags:
        • 1. Re: Counting transitions from one state to another
          Norbert Maijoor
          Norbert Maijoor Ambassador Jan 25, 2017 11:20 AM (in response to Ivan Rankenburg)

          Hi Ivan,

           

          Find my approach based on LOD expression and Quick Calculation "Difference" as reference below and stored in attached workbook version 9.3

           

          Count: {fixed [Spend Level],[Date]:count([UserID])}

          Transition: Count > Quick Calculation "Difference"

           

          • 2. Re: Counting transitions from one state to another
            Ivan Rankenburg
            Ivan Rankenburg Jan 25, 2017 11:29 AM (in response to Norbert Maijoor)

            Close...  You can count, daily, how many users are at each level, and you can count the change in the number of users, but this is not entirely what I need.

             

            According to your result, if A has 100 people in it on a given day, and 100 people in it the following day, you might conclude nobody joined or left level A.  Whereas in actuality, perhaps 10 people left level A and 10 people joined level A.  For example, according to your output, it appears nothing changed on the 4th and 5th, whereas in actuality, a lot changed.

             

            Each day, I need the complete matrix of transitions.  I need to know how many joined A (and where they came from), and how many people left A (and where they went to).

             

            Thanks for your attention!