
1. Re: Why does subtracting a Fixed calculation from another Fixed calculation double the subtraction?
swaroop.gantela Nov 7, 2019 5:09 PM (in response to Jeremy Locanas)1 of 1 people found this helpfulJeremy,
I'm not sure, but I'm wondering if having [Revenue] and [Total PS Cost] in
the dimensions part of the LODs might be causing the duplications.
Maybe they should just be:
InvoiceRevenue : {FIXED [Invoice#]:MIN(Revenue)}
and BillsCost : {FIXED [Bill#]:MIN(Total PS Cost)}

2. Re: Why does subtracting a Fixed calculation from another Fixed calculation double the subtraction?
Jeremy Locanas Nov 8, 2019 9:13 AM (in response to swaroop.gantela)Hi Swaroop  thanks for the reply. I tried that initially but it didn't produce the correct answers individually.
IE I have Invoice INV132913 with two revenue values $0 and $15,400.
That has 4 bills, 2 both with the id 633483 and values of $2632.84 and $2632.83,
then there are 2 other bills 101772 for $5214.29 and 633487 for $5134.62.
If I try it your way I end up with $0 for both all 3 revenue lines and then for bills I get row values of $2632.83, $5214.29, and $5134.62. Tableau autocombines 633483 into one row b/c it's a duplicate.
If I do it my way, I get all three correct values of $15400 for each revenue line, and $5266 for the combined 633483, $5135, and $5214 but the duplication occurs when combining as mentioned...

3. Re: Why does subtracting a Fixed calculation from another Fixed calculation double the subtraction?
swaroop.gantela Nov 8, 2019 11:57 AM (in response to Jeremy Locanas)Jeremy,
Thanks for the workbook, I think I see it better now.
I think you were on the right track,
maybe just need to add a second level of nested LOD:
InvoiceRevenue2:
{ FIXED [Invoice]:SUM(
{ FIXED [Invoice],[Revenue]:MAX([Revenue])}
)}
BillsTotal2
{ FIXED [Invoice]:SUM(
{ FIXED [Invoice],[Bill],[Cost]:MAX([Cost])}
)}
Please see workbook v2019.3 attached in the Forum Thread
to see if that is closer.

322335invoice.twbx 32.8 KB


4. Re: Why does subtracting a Fixed calculation from another Fixed calculation double the subtraction?
Jeremy Locanas Nov 8, 2019 2:41 PM (in response to swaroop.gantela)Hi Swaroop  I think this worked! I'm checking over as many entries as possible first, but thank you very much! Have a great weekend!

5. Re: Why does subtracting a Fixed calculation from another Fixed calculation double the subtraction?
swaroop.gantela Nov 8, 2019 3:42 PM (in response to Jeremy Locanas)Jeremy,
I just realized there may be a snag in the situation where (not sure how often it would happen)
in the raw data for billing there are truly two costs for the same amount in the same bill:
Bill# Cost
666485 $1044
666485 $1044
Because with the above calculated fields, this will only get counted once.
Is there another identifier like time of cost that would distinguish these two?

6. Re: Why does subtracting a Fixed calculation from another Fixed calculation double the subtraction?
Jeremy Locanas Nov 11, 2019 9:11 AM (in response to swaroop.gantela)Hi Swaroop  you are correct. I found 1 instance like that out of 30K rows so far out of 200K rows total. There is a location field that could be used, but I'm not 100% that would work. Where would you recommend inserting that [Location] at?
Thanks again!

7. Re: Why does subtracting a Fixed calculation from another Fixed calculation double the subtraction?
Jeremy Locanas Nov 11, 2019 9:23 AM (in response to Jeremy Locanas)Hi Swaroop  Location won't work as a unique identifier, sorry. I just found a 2nd example where all fields tie including location. Is there a way to create a new column to simply flag if there are duplicate Bill entries? I think that would suffice, as I don't think there is a way to account for 100% coverage considering Tableau's logic restrictions. If we have a flag, we can at least exclude those line items for doublechecking separately... Thanks!!!

8. Re: Why does subtracting a Fixed calculation from another Fixed calculation double the subtraction?
swaroop.gantela Nov 11, 2019 3:46 PM (in response to Jeremy Locanas)Jeremy,
I think the flagging method could work.
I made up a dataset with duplicates and tried this,
which I think is similar to the way you started:
[Flag Duplicate Item]:
IF { FIXED [Invoice],[Revenue],[Bill],[Cost]:SUM(1)}>1
THEN "Duplicate"
END
[Flag Invoice with Duplicate]:
{ FIXED [Invoice]:MAX([Flag Duplicate Item])}
Please see workbook v2018.3 and datasource attached in the Forum Thread.

322335Invoice.xlsx 8.3 KB

322335Bill.xlsx 8.5 KB

322335invoice2.twbx 68.1 KB


9. Re: Why does subtracting a Fixed calculation from another Fixed calculation double the subtraction?
Jeremy Locanas Nov 12, 2019 10:15 AM (in response to swaroop.gantela)Thank you Swaroop! That seems to have solved it, am rechecking all table entries again to reconfirm!