Blends do some strange (i.e. I don't understand them yet) things in terms of padding rows and nulls/non nulls. In cases like this the blend doesn't seem to return anything at all that can be evaluated. The way around that is to use the LOOKUP() table calculation. LOOKUP([field],0) finds the instance of that field in the view, and returns Null if there is no value. Then we can wrap that in the ZN() function, like so:
sum([RCVD_AMOUNT]) - ZN(LOOKUP(SUM([INV_PAID (Invoiced_PAID.xlsx)].[PAID_AMOUNT]),0))
I called this Balance Due. Then you can put this on the Filter shelf to filter for values that are at least 1.
Invoice_Dim jtd edit.twbx.zip 46.5 KB
The issue is that there are more INV_RCVD_NO than INV_PAID_NO instances. Since there is not data, for example, A006 in INV_PAID_NO Tableau cannot compare the two data sources. Using Jonathan's example manually gives a value that is not actually there. Another way of doing this is to create a calculated field that looks like the following:
iif(isnull((ATTR([INV_PAID (Invoiced_PAID.xlsx)].[INV_PAID_NO]))), 0, ((SUM([INV_PAID (Invoiced_PAID.xlsx)].[PAID_AMOUNT]))))
Hope this helps!
I'm curious, why would ISNULL(ATTR([INV_PAID (Invoiced_PAID.xlsx)].[INV_PAID_NO])) return True when there's no row being returned from the blend while ISNULL(SUM([INV_PAID (Invoiced_PAID.xlsx)].[INV_PAID_NO])) returns Null?
I've been wondering about this for a bit, and just submitted a support request to get an answer where I attached the following workbook. I'm used to databases that return Null when the left join doesn't return data, I'm trying to figure out what Tableau does.
Thanks Jonathan and Thanks Tracy,
I tried out both the methods.
When I use Jonathan method I am able to filter for the Invoices that are Not Paid and it works.
I tried the same using Tracys Method but I am not able to filter for the Invoices that are Not Paid.
It would be helpful if Tracy can have a look at the Sheet Tracy_Formula_Testing in the attached workbook. I am trying to drag the Calculated field TEST-TRACY_CALC1 to the Filters Card and only filter the Not Paid. The field TEST-TRACY_CALC1 does not stay in the Filters Card, I am not sure why.
Thanks once again,
Invoice_Dim.twbx.zip 60.4 KB
If you change the calculated field from discrete to continuous you will be able to add it to the filters shelf.
I have no idea why - just one of those quirks...
I was building this Report for a Customer who has around 1.4 Million Rows for Invoice received and 600 Thousand Rows in the Invoice Paid. Tableau just crashes when I bring the Invoice Received Number and Received Amount as Cross Tab View. I am not able to create the Filter. Any Suggestion would be helpful.
In response to the calculation not being able to be placed on the filter shelf, this is because calculated fields that are strings but use aggregated fields cannot be placed on the filter shelf. Tableau is unable to recognize them. To get around this, instead of using Paid, Not Paid use 0 and 1. So the calculation will look like the following:
IIF((sum([RCVD_AMOUNT]) - [TEST-TRACY_CALC]) <> 0, 1, 0)
Then, place this calculation on the level of detail shelf. Right click on it and select Continuous. Right click again and select Filter. Then, you can filter to one to show only the Not Paid members.
I'm not sure why Tableau is crashing--you might want to send this to our Support team at:
Be sure to include:
1. Screen shot of the error message, including the 'Show Details' information (if available)
2. Date and time of the issue
3. Log files (see http://www.tableausoftware.com/support/knowledge-base/sending-tableau-desktop-log-files for instructions on bundling and sending the log files)