13 Replies Latest reply on Aug 1, 2016 2:33 AM by Chris Dickson

# Communication between Tableau Worksheets rolled up on different variables

I have 4 difference worksheets in my tableau work book:

1)Some test dependent on first digits of amount billed to customer. Some of the digits will be aberrant. Example from 1 to 9, lets say 5 & 6 are aberrant.

2)Some test dependent on first two digits of amount billed to customer.Some of the digits will be aberrant. Example from 10 to 99, lets say 15 & 61 and 90 are aberrant.

3)Some test dependent on first three digits of amount billed to customer.

4)Comparison of total records vs records involving certain specific digits marked as aberrant in the first three sheets.

In the fourth sheet I want to pull al the records which only belong to aberrant digits. The problem is the first three digits are rolled up at digit level while the fourth sheet is rolled up at the customer level.

In the end I need something like this:

Customer 1  Total number of Transactions Total number of transactions involving Aberrant digits  Percentage of abeerant digits in total transactions.

Right now I have successfully creates the first three sheets and each sheet some of the digits are marked aberrant.

I am using this Aberrant variable as a filter in fourth sheet. But I am not getting any values here . PLease suggest how can I proceed on this.

• ###### 1. Re: Communication between Tableau Worksheets rolled up on different variables

Hi Mohit,

this is quite a detailed requirement, are you able to share a dummy workbook or dataset we could work with to create something for you?

• ###### 2. Re: Communication between Tableau Worksheets rolled up on different variables

I am sharing the dummy data. The first snapshot is a transaction data. The digits are aberrant on the basis of some statistical test and on the basis of that statistical score, I have ranked the digits. So a first digit(1-9), first two digits(10-99) are ranked on score basis and top three digits are marked aberrant.

So I have created separate sheets for each test(first digit, second digit & third digit). Each sheet has a variable called aberrant which two possible values Aberrant & Non Aberrant. Aberrant status would be for two digits(top two on the basis of score) in each test.

The last sheet should have the data at customer level and a snapshot is there in the excel sheet I have attached.

I managed to create that in tableau and connected the initial sheet as secondary data source. Then added the aberrancy variable as a filter, but still the results are coming for all the digits.

So how can I summarize my transaction data at customer level for both, all the digits as well as for aberrant digits.

Please let me know if you can help me on this

Regards

Mohit

• ###### 3. Re: Communication between Tableau Worksheets rolled up on different variables

This sounds like a variation on Benford's Law?

I have built the attached based on your requirements, there are better ways to achieve the boolean calculations if you are working out the aberrant values within tableau.

• ###### 4. Re: Communication between Tableau Worksheets rolled up on different variables

Bingo!! This is Benfords law

• ###### 5. Re: Communication between Tableau Worksheets rolled up on different variables

Hi Chris, I suppose you are working on the latest version of Tableau.I have Tableau 9 with me, thus unable to open it.

Can you help me with that.

• ###### 6. Re: Communication between Tableau Worksheets rolled up on different variables

if it is benfords law take a look at this article Visualizing Benford’s Law | Tableau Software

give me a few minutes and i'll upload a Tableau 9 version.

• ###### 7. Re: Communication between Tableau Worksheets rolled up on different variables

file attached

• ###### 8. Re: Communication between Tableau Worksheets rolled up on different variables

Chris ,

Thanks a lot for this article. But my problem is that I have got the Aberrant digits out of Benford, but I want to use those aberrant digits to pull the data corresponding to these digits, and I want it in a way that Tableu picks up aberrant digits from different sheets and by  creating either a parameter or filter to pull the suspicious data. Then I can compare the penetration of each customers by simply dividing the amount charged from aberrant digits by amount charged from all claims. This calculated percentage  can then be color coded with aberrant digits. So say suppose a customer has 30% data from aberrant digits it should show a bar for 30% and say I have 4 aberrant digits(two from each test, first digit & second digit), then the 30% bar should have sections from each aberrant digits marked from different color.What I have done is I have created a aberrant variable if digits are aberrant and then pass that variable as the filter in my final sheet.

• ###### 9. Re: Communication between Tableau Worksheets rolled up on different variables

ok so instead of hard coding the figures we want to highlight i have joined them as seperate tables, 1 table for each of 1,2 and 3 in length.

Then using a join highlighted those that need to be counted as aberrant?

• ###### 10. Re: Communication between Tableau Worksheets rolled up on different variables

Hi Chris,

Yes Exactly.

But how did you create this Type variable here.

Regards

Mohit

• ###### 11. Re: Communication between Tableau Worksheets rolled up on different variables

I hardcoded it on the file i attached containing the things i wanted to look up.

How flexible a solution do you need, i felt it would be easier to hard code as there were only 3 options from 3 separate left joins.

• ###### 12. Re: Communication between Tableau Worksheets rolled up on different variables

How did you get those different tables in the dimension window. What I am doing is blending data from different sheets. I have created the same solution as you suggested(but three different work sheets, one for each digit test), but when I see the percentage amount to benfords digit, all the digits are included in calculation instead of only aberrant units being True(in T/F created variable)

• ###### 13. Re: Communication between Tableau Worksheets rolled up on different variables

Hi Mohit,

instead of blending the tables i joined them in the data window. right click on your main data source and edit, i then dragged the extra tables in to the top right relational view and created left joins on them.

In the workbook i sent right click on the data source and edit to see what it looks like.