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

    Chia xinyi

      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
          Manoj Lobo

          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]

           

          Please refer the attached workbook

          9.png

          9.png

           

          Hope this helps!

           

          If this reply helps, mark it as helpful and correct

           

          Manoj

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

            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

              Hi Chia,

               

              Does this help?

               

              9.png

               

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

              9.png

              1st calculation

              Material 1:

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

               

              9.png

              2nd calculation

              Material 2:

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

              9.png

               

              Then calculate the difference using

              difference:

              [Material 1]-[Material 2]

              9.png

               

              Hope this helps!

               

              If this reply helps, mark it as helpful and correct

               

              Manoj

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

                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