Hi guys, So I have been stuck on this issue for a while now, tried a lot of different, even those I didn't fully understand.
So it goes like this:
I have to data sets, ClaimsDS and OSDS.
ClaimsDS contains the data for the particular claim, each entry is unique by the claim ID, policy ID, and date.
OSDS contains the data for only the claims that are outstanding, each entry is unique by claim ID, policy ID, and date.
So basically there is a one-one correspondence.
Both the databases are on the tableau server and live.
My task is to pull the amounts outstanding(OS) into the claims data and build some views, easy. Blend the data with the fields mentioned above and use sums, works.
The issue that I am facing, is when I have to use boolean/binary/flag (AR) with an aggregate measure.
I have a flag(AR) that tells me if there is supposed to be an anticipated recovery on the claim, it is in the ClaimsDS, and the anticipated recovery amount is the outstanding amount(OS) if the flag equals 1.
Now to pull the OS amount into claims, I just the SUM function in the calculated field, and tableau uses the relationship and views to aggregate data.
So I want in the same View, the total outstanding(OS), regardless of the flag, and the anticipated recovery, which basically is the outstanding amount(OS) when the flag(AR) is 1. I cant use a filter, nor an IF statement because mixing isn't allowed. How do I go about it?
The flag is in one database, the primary one, where all the other dimensions are present that are needed for building the views. The measure is in the other database, which is the secondary one, and is smaller, but carries less columns as well. I have to build a view with the sum of the measure in the secondary database and dimensions from the primary database. in the view, I have to show, the sum of the measure and some of the meaure with the flag.
I hope I have been clear enough.
I cannot attach a workbook, as of now because of the sensitivity if the data, I'd need approvals. Any push in any direction will be highly appreciated.