4 Replies Latest reply on Oct 13, 2019 11:28 PM by Chia xinyi

# How do I subtract 2 or more value in the same filter

How can I subtract Denim from Cotton ?

Or Rubber - Denim.

I want to create a calculation field that automatically help me to subtract the value of the items I filter.

• ###### 1. Re: How do I subtract 2 or more value in the same filter

Hi

Create 3 calculated field like this,

Cotton Price: { FIXED :SUM(IF [Material type]="Cotton" then [Price] END)}

Rubber Price:{ FIXED :SUM(IF [Material type]="Rubber" then [Price] END)}

Denim Price:{ FIXED :SUM(IF [Material type]="Denim" then [Price] END)}

Then create another calculated field for difference

Rubber-Cotton:

[Rubber price]-[Cotton price]

Hope this helps!

Manoj

• ###### 2. Re: How do I subtract 2 or more value in the same filter

As I had another set of data inside have like 100,000++ of data so is it possible for me to write only one calculation field and apply for all?

There will be many more material type in my data. Thus, I will filter them.

The calculation field that I want to write is like:

- If 2 items selected in the filter than

- Then the calculation field will help me to find the different between them

• ###### 3. Re: How do I subtract 2 or more value in the same filter

Hi Chia,

Does this help?

Create 2 parameters from the field material type and use the following calculation

1st calculation

Material 1:

{ FIXED :SUM(IF [Material type]=[Material type Parameter 1] then [Price] END)}

2nd calculation

Material 2:

{ FIXED :SUM(IF [Material type]=[Material type Parameter 2] then [Price] END)}

Then calculate the difference using

difference:

[Material 1]-[Material 2]

Hope this helps!

Manoj

• ###### 4. Re: How do I subtract 2 or more value in the same filter

How do I write the calculation is I want to change the offset number according to the number of options I choose in the filter ?

For example: In my filter I have A,B,C,D,E,F,G,H

So my equation is like Sum([Price])-LOOKUP(ZN(SUM([Price])),5)

Number 5 is my offset number and this number will change according to the number of options I choose in the filter. So if I choose A,B,C then the offset number change to 3 instead of 5