3 Replies Latest reply on Sep 14, 2016 10:26 AM by Santiago Sanchez

    Counting transactions over period of time

    Aniket Gode
      RecipientGroupMonth Year
      72122015
      92122015
      82122015
      13122015
      62122015
      31122015
      53122015
      32122015
      23122015
      42122015
      1312016
      5112016
      7212016
      12312016
      8112016
      5322016
      3322016

      consider above data set,

       

      These are the transactions snapshot in time every month.

       

      I want to find out the frequency of the group change.  This data is a snapshot in a month.

       

      So for example.

       

      if recipient 1 changed from group 3 to group 4 and back to group 3 again. Frequency of the change will be counted as 2.

       

       

      How do we do that in tableau ?

       

      Thank you for your help!

       

      Above data set should give result like

      Recipient Frequency of change

      5- 2

      7-0

      1-0

      and so on....

        • 1. Re: Counting transactions over period of time
          Santiago Sanchez

          Another way to express this may be: the different groups each recipient has been assigned to. You can write a calculated field for this like:

           

          COUNTD([Group])

           

          However, because you don't need the number of groups but the number of changes, you can substract 1 (i.e. if a customer is only in one group, 0 should be displayed instead of 1):

           

          [Group Changes] = COUNTD([Group]) - 1

           

          If you drag that along Recipient to the Viz you'll get something similar to the workbook attached.

          • 2. Re: Counting transactions over period of time
            Aniket Gode

            Thanks Santiago!

             

            This wont be always true though, imagine

             

            Recipient 1 moves from 3 to 5, then 5 to 3, then 3 to 8, then 8 to 3.

             

            In this case total changes as 4, but according to your calculation I would get 2.

             

            How do we solve this problem?

            • 3. Re: Counting transactions over period of time
              Santiago Sanchez

              Well spotted! We can probably use a table calculation to help us [Current vs. Last]:

               

              IF LOOKUP(ATTR([Group]), -1) <> LOOKUP(ATTR([Group]), 0)

              THEN 1

              ELSE 0

              END

               

              LOOKUP takes a look at what's on the visualization. -1 looks at the prior value, 0 looks at the current (for this to work, Group and Date need to be on the Viz to make valid comparisons). With that, we can create diferent types of visualizations like the 'Bar Chart' sheet on the workbook attached. Make sure you have the table calculation setup like this:

               

              Groups.png

              You can follow a similar logic to get a total with thise table calculation: [Total Changes] = WINDOW_SUM([Current vs Last]). See examples on 'Crosstab + Filter' and 'Bar Chart + Filter ' sheets.

              1 of 1 people found this helpful