Forum Discussion
Create a Calculated Column from Different Tables
Hi Stachu
Apologies for too many complications.
I have to create something similar to date parameter in Tableau. I need to report the client status at the end of every month. This includes opening and closing balances, new and lost clients etc.
All fields are related to period end dates and live dates for the client. Eg:
New clients = if(Live date = period end date, new)
Opening balance = if(Live date = (period end date -1), balance)
Closing balance = balance as on period end date etc.
I’ll add a slicer in the end for users to see data for previous months and it should filter data specifically for that month.
Steps I followed
1. Created a new table with period end dates (that will remain static)
2. Added another column for month and year of period end date
3. Ceated a relationship to join month and year of period end date to month and year of live date
Now, based on these steps, the data is not getting populated.
Can you tell me a better and easy way to get right data.
Thanks
I still don't get the logic, it looks inconsistent to me
you write:
All fields are related to period end dates and live dates for the client. Eg:
New clients = if(Live date = period end date, new)
but in the table you posted:
| Today | Live date | Client ID | Actual status | Status required |
| 26-Oct | 30-Sep | A00034 | new | new |
| 26-Oct | 26-Oct | 12303 | old | new |
according to that table for the customer 12303 the status should be new, even though 26-Oct is not in an end date (31 Oct is)
do you mean that when October is selected the flag should say "new" because client was added in the month of October?
so does 'end date' mean full month?
Opening balance = if(Live date = (period end date -1), balance)
-1 means one month, not one day, right?