2 Replies Latest reply: Jul 27, 2018 1:39 AM by Petter Skjolden RSS

    Max and percentage calculation help

    Mahitha M

      Hi Experts,

       

      Can any one please help me to do below two calculations. I am very new to these calculations. Please help me on this.


      CALCULATIONS:


      1. For each branch ,need to derive FTE based on below calculation

      FTE = Max(0,VALUE+Max(AMOUNT)/5)+0.01

       

      2. For each Name and each BRANCH Name need to calculate the below percentage column based on below calculation


      TME%=SUM OF FTE IN THE BRANCH/SUM OF ALL FTE.

       

      I have the fields with sample data like below

      Name        BRANCH             VALUE            AMOUNT

      KELLY      Sales                   1258693           53685

      KELLY      Sales                   4869306           96873

      KELLY      Products              4869386           96863

      KELLY      Products              4869869           92639

      JOHN       ISON                    78563.58           86943

      GOA        OURMT                 8563216           869321

      GOA         Products              4869869           9686353




       


      Thanks in advance.


        • Re: Max and percentage calculation help
          Petter Skjolden

          This might be in the direction you want:

           

          2018-07-26 16_48_23-Microsoft Edge.png

           

          The FTE is a measure with the following expression and label as FTE:

           

              Sum(VALUE)+Max(TOTAL AMOUNT)/5+0.01

           

          The TME% is a measure witth this expression and label as TME%:

           

              FTE / RangeSum(Above(FTE,0,RowNo(TOTAL)))


          In the above expression FTE refers directly to the FTE column calculated values but it could also be written:


             Sum(VALUE)+Max(TOTAL AMOUNT)/5+0.01 / Above( Sum(VALUE)+Max(TOTAL AMOUNT)/5+0.01 , 0 , RowNo(TOTAL)))


          obviously the expression with references to the FTE column is to prefer....

           

            • Re: Max and percentage calculation help
              Petter Skjolden

              It should be very similar like this:

               

              FNE:

              Sum(VALUE)-Max(TOTAL AMOUNT)/5-0.01


              TNE%:

              FNE / RangeSum(Above(FNE,0,RowNo(TOTAL)))

               

              I have assumed that the outer aggregation of Max in the FTE and the outer aggregation of Min in the FNE is just functions to pick the lowest for two values: 0 or the calculation and might not be necessary the way Qlik handles the calculations. I might be wrong since I don't know the full nature of your data with such a small sample.

               

              So for this reason it might be necessary to wrap both the FTE Max and FNE Min in RangeMax and RangeMin functions that would be similar to the Excel Min and Max functions.

               

              FTE would then be:

              RangeMax( 0 , Sum(VALUE)+Max(TOTAL AMOUNT)/5+0.01 )

               

              FNE would then be:

              RangeMin( 0 , Sum(VALUE)-Max(TOTAL AMOUNT)/5-0.01 )