5 Replies Latest reply on Oct 12, 2017 8:17 AM by Francesca Grisanti

# Difference between 2 Columns

Dear All,

Kindly help me with calculating the difference between two Columns.

Below is the calculated field used.

Case [SalesViewBy]

When "Gross" then Sum(

If [Order Status (group)]='Confirmed' then [Sale Value]

ELSEIF [OrderStatus]='Cancelled' then [CancelledSaleValue]

END)/10000000

When "Unit" then

Countd(

If [Order Status (group)]='Confirmed' then [Unit ID]

ELSEIF [OrderStatus]='Cancelled' then [CancelUnitID]

END)

When "Area" then Sum(

If [Order Status (group)]='Confirmed' then [Saleable Area]

ELSEIF [OrderStatus]='Cancelled' then [CancelSaleableArea]

END)

END

This is the output. I need the difference between Confirmed and Cancelled

MonthConfirmedCancelledDifference
Grand Total125.198.53116.66
April36.782.7734.01
May31.4331.43
June47.385.7641.62
July9.609.60

Regards,

Keerthi KS

• ###### 1. Re: Difference between 2 Columns

Hi Keerthi,

It'll be helpful for us if you share some sample mock up in packaged workbook.

Mahfooj

• ###### 2. Re: Difference between 2 Columns

Hi,

You can using table calculation (difference), but to give the exact formula and the scope need to know how you organize your dimension in VIZ ...

Best Regards

• ###### 3. Re: Difference between 2 Columns

Hi!

You can re-calculate your measure like this:

if last()=first() then

//GRAND TOTAL

sum( if ([yourattribute]) =  'Confirmed'  then ([measure]) else ([measure])*-1 end)

else sum([measure])

end

You can try re-adapting this formula!

Hope it can help!

1 of 1 people found this helpful
• ###### 4. Re: Difference between 2 Columns

I have changed the Calculated field. but its not coming for UnitID.

Case [SalesViewBy]

When "Gross" then Sum(

If [OrderStatus]='Cancelled' then -[CancelledSaleValue]

ELSE [Sale Value]

END)/10000000

When "Unit" then

Countd(

If [OrderStatus]='Cancelled' then -[CancelUnitID]

ELSE [Unit ID]

END)

When "Area" then Sum(

If [OrderStatus]='Cancelled' then -[CancelSaleableArea]

ELSE  [Saleable Area]

END)

END

• ###### 5. Re: Difference between 2 Columns

At this point I think I need a packaged workbook in order to see and better understand data and needs.