3 Replies Latest reply on May 4, 2017 1:40 PM by Jaspreet Ghuman

# calculated field - how to do it ?

Table1 -

sales

order_id Sales

1 100

2 200

3 300

4 400

Table 2

Refund

order_id Sales

2 200

4 400

Will leftouter between sales and refund on order id

How to calculate following using calculated field

Now total sales - 1000

refund is - 600

Original Sale - 400

• ###### 1. Re: calculated field - how to do it ?

Hi Kunal,

After you join the 2 datasets on order_id sales field getting the total sales - 1000 and refund is - 600 should be pretty straightforward. For Original sale, create a calculated field in any of the 2 table : sum(sales)-sum(refunds). This field should give the required result which is 400.

Cheers,

Jas

• ###### 2. Re: calculated field - how to do it ?

Thank you Jas for your reply-.-. It was my bad that the initial data set was not the correct one, The actual data set would be

Table1 -

sales

order_id Sales

1 100

2 200

3 300

4 400

Table 2

Refund

order_id Refund_index

2 Y

4 Y

Will leftouter between sales + and refund on order id

data set would be

order_id Sales order_id(refund)

1 100 null null

2 200 2     Y

3 300 null null

4 400  4    Y

How to calculate following using calculated field

Now total sales - 1000

refund is - 600

Original Sale - 400

• ###### 3. Re: calculated field - how to do it ?

Hi Kunal,

In this scenario, first create a calculated field for refunds - if [Sales (Refund)]='Y' THEN [Sales] ELSE 0 END

Then create second calculated field for original sale using Sales and the newly created calculated field for refunds.

Cheers,

Jas

1 of 1 people found this helpful