I have been trying to perform joins on the fly in Tableau to perform some online computation - with no luck so far.
I wonder if any of you is aware of a way to achieve this?
I have a typical transactions dataset ("MYDATA"), with user ID (user's identifier), transaction date (when the transaction occurred), and purchases (the transactions). Something like:
ID TRANSACTION DATE PURCHASES
123 20/03/2020 1
123 22/03/2020 4
234 20/03/2020 10
234 22/03/2020 1
345 22/03/2020 5
What I would like to achieve is to add to it a variable with the SUM of PURCHASES by ID (say field "PURCHASES PER ID").
Then, critically, I'd like to make this computation update dynamically as I filter by different values in TRANSACTION DATE from the UI.
Ultimately I'd like to create a chart displaying the count of users (field "ID") in each value of the field "PURCHASES PER ID" (like bins), where "PURCHASES PER ID" is re-computed according to the date ranges selected in the worksheet.
Something like:
Case 1 : FILTER Transaction date = 20/03/2020 AND 22/03/2020
|---------------------|------------------|
| count OF ID | SUM of PURCHASES |
|---------------------|------------------|
| 2 | 5 |
|---------------------|------------------|
| 1 | 11 |
|---------------------|------------------|
Case 2 : FILTER Transaction date = 20/03/2020
|---------------------|------------------|
| count OF ID | SUM of PURCHASES |
|---------------------|------------------|
| 1 | 1 |
|---------------------|------------------|
| 1 | 10 |
|---------------------|------------------|
I'd expect this to be doable in Tableau, as I'm able to it with a much more simple (and cheaper) tool like Google Data Studio.
In Data Studio I'd simply do a join between "MYDATA" and the sum of PURCHASES grouped by ID - using ID as KEY. Then, I'd able to use that calculated sum of purchases as a dimension, and count the IDs in it.
Are you aware of a way to achieve the same in Tableau?
Many thanks
