7 Replies Latest reply: Aug 9, 2018 3:32 PM by Petter Skjolden RSS

    Excel calcualtions help

    Mahitha M

      Hi Experts

       

      Can any one please help me to calculate Calcolumn1,Calcolumn2, Calcolumn1% and Calcolumn2% in the below attached app.

       

      Thanks in advance.

        • Re: Excel calcualtions help
          Petter Skjolden

          The formulas in the post text does not align with the formulas in the attached Excel sheet. Could you please either update your explanation or the Excel sheet so the questions makes sense and is consistent?

            • Re: Excel calcualtions help
              Mahitha M

              Hi petter-s,

               

              Thanks for your reply.

              Sure I will try to update my text more clear.I am new to these calculations

              Please consider the excel calculations only last 4 columns are the output columns which need to be calculated in Qlik.

              Please help me on this.

               

              Thanks in advance.

                • Re: Excel calcualtions help
                  Petter Skjolden

                  Here is a way of doing it in Qlik Sense:

                   

                  2018-08-09 19_36_19-Film og TV.png

                   

                  Calcolumn1:

                   

                  =RangeMax(0,Sum(Value)) + RangeMax(Sum(Item1),Sum(Item2),Sum(Item3),Sum(Item4),Sum(Item5),Sum(Item6),Sum(Item7),Sum(Item8),Sum(Item9),Sum(Item10),Sum(Item11))/5+0.1
                  
                  

                   

                  Calcolumn2:

                   

                  =-RangeMin(0,Sum(Value)) +  RangeMax(Sum(Item1),Sum(Item2),Sum(Item3),Sum(Item4),Sum(Item5),Sum(Item6),Sum(Item7),Sum(Item8),Sum(Item9),Sum(Item10),Sum(Item11))/5+0.1
                  
                  

                   

                  Calcolumn1%:

                   

                  Sum(TOTAL <SCOPE>
                  Aggr(
                    RangeMax(0,Sum(Value)) + RangeMax(Sum(Item1),Sum(Item2),Sum(Item3),Sum(Item4),Sum(Item5),Sum(Item6),Sum(Item7),Sum(Item8),Sum(Item9),Sum(Item10),Sum(Item11))/5+0.1
                  , Date, SCOPE, Name, Category )
                  )
                  /
                  Sum(TOTAL
                  Aggr(
                    RangeMax(0,Sum(Value)) + RangeMax(Sum(Item1),Sum(Item2),Sum(Item3),Sum(Item4),Sum(Item5),Sum(Item6),Sum(Item7),Sum(Item8),Sum(Item9),Sum(Item10),Sum(Item11))/5+0.1
                  , Date, SCOPE, Name, Category )
                  )
                  
                  

                   

                  Calcolumn2%:

                   

                  Sum(TOTAL <SCOPE>
                  Aggr(
                    RangeMax(0,-Sum(Value)) + RangeMax(Sum(Item1),Sum(Item2),Sum(Item3),Sum(Item4),Sum(Item5),Sum(Item6),Sum(Item7),Sum(Item8),Sum(Item9),Sum(Item10),Sum(Item11))/5+0.1
                  , Date, SCOPE, Name, Category )
                  )
                  /
                  Sum(TOTAL
                  Aggr(
                    RangeMax(0,-Sum(Value)) + RangeMax(Sum(Item1),Sum(Item2),Sum(Item3),Sum(Item4),Sum(Item5),Sum(Item6),Sum(Item7),Sum(Item8),Sum(Item9),Sum(Item10),Sum(Item11))/5+0.1
                  , Date, SCOPE, Name, Category )
                  )
                  
                  

                   

                  I have attached the full example where the screenshot is taken from as a QVF-file for your testing...

                   

                  I have taken the Excel table as is but I would highly recommend that this table should be unpivoted so the Item1...Item11 would appear as rows instead of columns in the table in Qlik Sense. Then the expressions in Qlik Sense would be different. I might give you a second Qlik Sense app to show how this is done - a little bit more evolved and a better solution in Qlik Sense to be honest.