7 Replies Latest reply on Jun 6, 2018 8:37 AM by TarunDeep Chhabra

Subtraction between domains of a dimension

Each row in my Excel file represents the document for a single transaction. The columns contain info such as Type (i.e. Invoice, Returns, etc), Doc Amount, currency ID, exchange rate, customer name, etc..

My current visualization only shows the total IN (invoice) and RT(return) amounts per customer. I'd like for it to show IN less RT, in order to see overall sales per customer. Also, I'd like for exchange rate to be taken into account. How do I do these?

(Type is a dimension and Doc Amount is a measure. When trying to create a calculated field, IN and RT are not displayed under 'Fields', hence I'm not sure how to insert them into the formula. I'm not familiar with formulas either.)

Thank you.

• 1. Re: Subtraction between domains of a dimension

Hi Isabel,

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:

sum([Invoice])-sum([Returns])

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!

-Tracy

• 2. Re: Subtraction between domains of a dimension

Hi Tracy,

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.

- Isabel

• 3. Re: Subtraction between domains of a dimension

Hi Isabel,

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?

-Tracy

• 4. Re: Subtraction between domains of a dimension

Hi Tracy,

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.

• 5. Re: Subtraction between domains of a dimension

Hi Isabel,

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.

Jonathan

1 of 1 people found this helpful
• 6. Re: Subtraction between domains of a dimension

Hi Jonathan,

Thank you for your clarification. I did indeed misunderstand Tracy's instruction.

Isabel

• 7. Re: Subtraction between domains of a dimension

Tried it but Tableau gives null values for this calculation.