6 Replies Latest reply: Nov 15, 2016 5:15 PM by Lech Miszkiewicz RSS

    EXPRESSION MODIFICATION

    PRIYANTHA BANDARA

      Dear Experts,

       

      I have a pivot table which looks like below,

       

      BCOD MO_NM CLA_CODE EFF_MONTH JanFebMarAprMayJunJulAugSepOct
      B100MOTORM3148916158661164360158743158506140203186970202715155078178589
      M437313-88923112722245386123106635182910307103021120
      MC2117618217575319130491858664197562017989442124056225474724743382893513
      Total 2303847 2325522 2108536 2039653 2172738 1970213 2346209 2548491 2700447 3093222
      NON_MOTOREN116400-6600034315588003000030155034000320952665079580
      FR1453132456711997084460-637914531020684256702401728142
      HH80135125328759232523690-10522046977-7215-1703410641
      MS4865216056465028156041218236178873219810178535-21717-18022
      PP43439389653456517289217262667737848430332947632494
      SS52700282003700-282002820056400-84600479602996040460
      TC4676114534518972913461818981849486279052166747230740255439
      TT3300-------625043450
      Total 457366 366766 480182 515333 505291 653076 553772 486825 524497 452183

       

      In this presentation only expression is SUM(AMOUNT_2016)

       

      My problem is how can  i modify above expression to achieve percentages from the total  instead of above figures. then the total must be 100.

       

      Rgds,

      Priyantha.

        • Re: EXPRESSION MODIFICATION
          Sushil Kumar

          Hi,

           

          Try this expression:

           

          SUM(AMOUNT_2016)/SUM(Total<CLA_CODE>AMOUNT_2016)


          HTH

          Sushil

            • Re: EXPRESSION MODIFICATION
              PRIYANTHA BANDARA

              Dear Sushi,

               

              I modified with your assistance and got the following expression.

               

              ROUND(SUM(AMOUNT_2016)/SUM(Total<MO_NM>AMOUNT_2016),0.0001)

               

              But expected output does not achieved.

               

              Look at the resulted output.

               

              BCOD MO_NM CLA_CODE EFF_MONTH JanFebMarAprMayJunJulAugSepOct
              B100MOTORM30.00630.00670.00700.00670.00670.00590.00790.00860.00660.0076
              M40.0016-0.00040.00130.00090.00160.00130.00150.00390.00300.0009
              MC0.08970.09220.08100.07870.08370.07620.09000.09550.10480.1226
              Total 0.0976 0.0985 0.0893 0.0864 0.0920 0.0835 0.0994 0.1079 0.1144 0.1310
              NON_MOTOREN0.0233-0.01320.00690.01180.00600.06040.00680.00640.00530.0159
              FR0.02910.00490.02400.0169-0.00130.02910.00410.00510.04810.0016
              HH0.00020.00700.00660.01850.0047-0.02110.0094-0.0014-0.00340.0021
              MS0.00970.03210.01300.03120.04370.03580.04400.0357-0.0043-0.0036
              PP0.00870.00780.00690.00350.00430.00530.00760.00860.00590.0065
              SS0.01050.00560.0007-0.00560.00560.0113-0.01690.00960.00600.0081
              TC0.00940.02910.03800.02690.03800.00990.05590.03340.04620.0511
              TT0.00070.00000.00000.00000.00000.00000.00000.00000.00130.0087
              Total 0.0916 0.0734 0.0961 0.1032 0.1012 0.1307 0.1109 0.0975 0.1050 0.0905

               

              with this calculation total raw must have the 1.0000 as the answer in both Motor and Non_Motor

               

              Can you please find the mistake i have done here.

            • Re: EXPRESSION MODIFICATION
              Lech Miszkiewicz

              It is important also to determine dimensionality - so the question is whether your percentage should be calculate from subtotal on CLA_CODE or maybe MO_NM level or on any other level. Then ultimetly change value in those brackets

              <CLA_CODE> as per Sushil's post

               

              regards

              Lech

              • Re: EXPRESSION MODIFICATION
                Chhavi Singhal

                Hi Priyantha,

                Please have a look at the attached QV.

                • Re: EXPRESSION MODIFICATION
                  Lech Miszkiewicz

                  Hi,

                   

                  I quess this is what you wanted:

                  =sum(AMOUNT)/SUM(Total<MO_NM, EFF_MONTH>AMOUNT)

                   

                  thath gives you this result:

                  Capture.PNG

                  If you need different calculation, maybe provide and XLS example what you're trying to achieve.

                   

                  kind regards

                  Lech