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

# Counting transactions over period of time

 Recipient Group Month Year 7 2 12 2015 9 2 12 2015 8 2 12 2015 1 3 12 2015 6 2 12 2015 3 1 12 2015 5 3 12 2015 3 2 12 2015 2 3 12 2015 4 2 12 2015 1 3 1 2016 5 1 1 2016 7 2 1 2016 12 3 1 2016 8 1 1 2016 5 3 2 2016 3 3 2 2016

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 ?

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

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

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

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:

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