
1. Re: How do I subtract 2 or more value in the same filter
Manoj Lobo Oct 11, 2019 3:28 AM (in response to Chia xinyi)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
RubberCotton:
[Rubber price][Cotton price]
Please refer the attached workbook
Hope this helps!
If this reply helps, mark it as helpful and correct
Manoj

pricediff_v10.5.twbx 16.0 KB


2. Re: How do I subtract 2 or more value in the same filter
Chia xinyi Oct 13, 2019 6:01 PM (in response to Manoj Lobo)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
Manoj Lobo Oct 13, 2019 7:32 PM (in response to Chia xinyi)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!
If this reply helps, mark it as helpful and correct
Manoj

pricediff_v10.5.twbx 17.9 KB


4. Re: How do I subtract 2 or more value in the same filter
Chia xinyi Oct 13, 2019 11:28 PM (in response to Manoj Lobo)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