7 Replies Latest reply on Feb 8, 2017 11:39 PM by Mahfooj Khan

# Calculated field for below logic

Hello Everyone,

Please find the attachment & Below screenshot. I need to show 1806250 as a output but not 11120200. I am unable to provide sample workbook as well. Please help me out with the logic. Regards,

Koushik.

• ###### 1. Re: Calculated field for below logic

Hi Koushik try to use to fixed LOD calculation so that quantity and difference column would not be aggregated before multiplication

hope this will work

• ###### 2. Re: Calculated field for below logic

Hello Nipun,

I tried taking FIXEd..but unable to get the proper Value.

Regards,

Koushik

• ###### 3. Re: Calculated field for below logic

Hi Koushik,

Not sure this will work or not? I haven't validated this calculated field.

WINDOW_SUM((SUM(IIF([Category]='Books',[Profit],0))-

SUM(IIF([Category]='Cases',[Profit],0)))*

SUM([Quantity]))

Compute using Table (down)

Mahfooj

• ###### 4. Re: Calculated field for below logic

Mahfooj i will try to implement thanks for your reply

• ###### 5. Re: Calculated field for below logic

Koushik,

Let me know If it works

Whenever you've such requirement where you've do some complex calculation then try to split your calculation in small-small results. Like in your case first try to calculated the Profit for Books and Cases separately and validate on canvas check correct output is coming or not. If you're getting correct then you're on right path

Once done then get the difference. Then after multiply with quantity. Then sum it up using WINDOW(). I alway follow this method and it works.

Mahfooj

• ###### 6. Re: Calculated field for below logic

Mahfooj,

Its not working

Regards,

Koushik

• ###### 7. Re: Calculated field for below logic

Okay

I don't have tableau access right now else I must answer with correct calculation.

Are you able to calculated Profit for Books and Cases separately?

Split the calculations and check till get

What this calculated field is returnning?

[Books]: SUM(IIF([Category]='Books',[Profit],0))

[Cases]: SUM(IIF([Category]='Cases',[Profit],0))

Diff: [Books]-[Cases]

Diff*Qty: [Diff]*sum([Qty]) //I've doubt at Qty

Then window_sum([Diff*Qty])

Or we've to take help from LODs

Do you've any other field on rows? Books/Cases values from on field right?

Can you show me exact format of your original data set? How it looks?