To get Invoice and Returns to have values separated from each other, a couple calculated fields will need to be created.
if [Type]='Invoice' then [Doc Amount] end
if [Type]='Returns' then [Doc Amount] end
Then, if these want to be subtracted from one another:
I'd have to see how the data is set up in terms of the exchange rate, but I imagine a calculated field could be applied to each of the calculations for it to be taken into account.
Hope this helps!
Thank you for your response! I've tried the formula and it seems there is a syntax error at the start of the second line. What could it be?
There is a column for Exchange Rate, which states the exchange rate if the transaction was not carried out in local currency.
If Doc Amount is aggregated, then Type will need some sort of aggregation on it as well. Try:
if attr([Type])='Invoice' then [Doc Amount] end
If this doesn't solve it, what is the error you are receiving?
I've tried your suggstion and it remains the same.
It says "syntax error (maybe you are missing an operator)". It's underlining "i" at the start of the second line.
1 of 1 people found this helpful
I'm wondering if you missed the bit of Tracy's instruction to create two separate calculated fields for the two IF statements? If you try to put both IF statements into the same field, you will get the error you describe.
Thank you for your clarification. I did indeed misunderstand Tracy's instruction.
Tried it but Tableau gives null values for this calculation.