5 Replies Latest reply: Jul 31, 2018 4:33 AM by Muhammad Nur Akmal Mohd Halim RSS

    how to calculate row by row with different calculation

    Muhammad Nur Akmal Mohd Halim

      Hi master. is there any way to have a different calculation by row? lets say that (999 * 25) + 100 -1 +(47/20). the issue now i had problem to point that 999, 25, and the rest since use 2 dimension. kindly to have your help. tq in advance.

      Capture.PNG

        • Re: how to calculate row by row with different calculation
          Sibin Jacob.C C

          Hi Muhammad,

           

          We can write different calculation for each row by using the Dimension value hard coded or using the Rowno() function.

           

          For example Using Rowno():

          If (Rowno()=1, 100+50,

          If (Rowno()=2, 100-50))


          For example Using Dimension value hardcoded:


          if(Parameter ='pH', 100+50,

          if(Parameter ='Viscosity', 100-50))


          Not sure I fully understand what you exactly want to get, Please sharesample application.


          Thanks,

          Sibin

          • Re: how to calculate row by row with different calculation
            Sibin Jacob.C C

            Hi,

             

            I am assuming,the straight table having one dimension (Test Parameter) and 3 expressions.

             

            and you need a specific calculation for Total column in "3AM" and "7AM"

             

            Go to expression tab , Total Mode -> select "Expression Total" option.

            then change expression definition of 3AM as below

             

            if(isnull(RowNo()),(999 * 25) + 100 -1 + (47/20),

            existing expression)

             

            expression definition of 7AM


            if(isnull(RowNo()),(10*19) + 200 - 2 + (45/21),

            existing expression)

             

            Thanks,

            Sibin

            • Re: how to calculate row by row with different calculation
              Jayaseelan K

              Hi,

               

              Correct if i understand you question wrong,

               

              This is your formula: (999 * 25) + 100 -1 + (47/20)

               

              You can write a expression like:

              Expression 03:00 AM:

              (Sum({<TestParameter={'pH(5-10)'}>}03:00 AM) * Sum({<TestParameter={'TSC % (20% - 50%)'}>}03:00 AM) ) + Sum({<TestParameter={'viscosity(cp)'}>}03:00 AM)-Sum({<TestParameter={'Particle Size(2.269um)'}>}03:00 AM)+(Sum({<TestParameter={'Seive Residue (45um)'}>}03:00 AM) / Sum({<TestParameter={'Seive Residue (20um)'}>}03:00 AM))

               

              Expression 07:00 AM:

              (Sum({<TestParameter={'pH(5-10)'}>}07:00 AM) * Sum({<TestParameter={'TSC % (20% - 50%)'}>}07:00 AM) ) + Sum({<TestParameter={'viscosity(cp)'}>}07:00 AM)-Sum({<TestParameter={'Particle Size(2.269um)'}>}07:00 AM)+(Sum({<TestParameter={'Seive Residue (45um)'}>}07:00 AM) / Sum({<TestParameter={'Seive Residue (20um)'}>}07:00 AM))


              Expression 11:00 AM:

              (Sum({<TestParameter={'pH(5-10)'}>}11:00 AM) * Sum({<TestParameter={'TSC % (20% - 50%)'}>}11:00 AM) ) + Sum({<TestParameter={'viscosity(cp)'}>}11:00 AM)-Sum({<TestParameter={'Particle Size(2.269um)'}>}11:00 AM)+(Sum({<TestParameter={'Seive Residue (45um)'}>}11:00 AM) / Sum({<TestParameter={'Seive Residue (20um)'}>}11:00 AM))

               

              Thanks,

               


               

                • Re: how to calculate row by row with different calculation
                  Muhammad Nur Akmal Mohd Halim

                  thanks for ur help sir.

                  unfortunately my client ask me another way around.

                  lets say total is been calculated by within range (black colour).

                  they want :

                   

                  total for 3AM= (3(black) / (6(All colour)) *100 = 50%.

                  total for 7AM= (4(black) / (6(All colour)) *100 = 66.6%.

                   

                  is it possible? maybe u can share other mechanism instead of color

                   

                  i share the query for color.

                   

                  if([Test Parameter] like 'pH (5-10)' AND [03:00AM]>=5 AND [03:00AM]<=10,BLACK(),

                  if([Test Parameter] like 'TSC% (20%-50%)' AND [03:00AM]>=20 AND [03:00AM]<=50,BLACK(),

                      if([Test Parameter] like 'Viscosity (cp)' AND [03:00AM]>=200 AND [03:00AM]<=500,BLACK(),

                          if([Test Parameter] like 'Particle Size (<2.269µm)' AND [03:00AM]<3,BLACK(),

                              if([Test Parameter] like 'Sieve Residue (45µm)' AND [03:00AM]=45,BLACK(),

                                  if([Test Parameter] like 'Sieve Residue (20µm)' AND [03:00AM]=20,BLACK(),rgb(256,256,256))

                  Capture.PNG