3 Replies Latest reply: Jun 5, 2017 2:18 PM by Sunny Talwar RSS

    Calculate a grand, row, column total

    Jose Hoyos

      Hi,

      I'm pretty new to Qlik and this community so i hope i can get advise for solving this calculation problem.

       

      I have a requirement for a KPI  (Index) the formula to calculate it looks like this.

       

      Index =  ( Cell Value X Grand Total) / (Row total X Column Total)

      Table.PNG

      Other requirements:

      > the output of this calculation will be shown in on one pivot table, with only one row dimension.

      > The user will have a week period filter in the report where he can select one or more weeks . The input for the formula has to be the value of the max selected week period (like week 5) and calculate  max 5 weeks back. 

       

      I have tried for the grand total the following formula, but the report 'filters' keep messing up the results.

       

      Grand Total: sum({$<week= {">=$min(week(day,0) -5 ))<=$(=max(week(day,0)))"}>} TOTAL facts)

       

      For the Row and Column totals i think an "AGGR" Function is the solution but havent been able to get it working.

       

      I am very thankful for all your advice ...

      Greetings

      Jose

        • Re: Calculate a grand, row, column total
          Sunny Talwar

          May be like this?

           

          Sum(Aggr(

          (Sum({$<week= {">=$min(week(day,0) -5 ))<=$(=max(week(day,0)))"}>} facts) *

          Sum({$<week= {">=$min(week(day,0) -5 ))<=$(=max(week(day,0)))"}>} TOTAL facts))

          /

          (Sum({$<week= {">=$min(week(day,0) -5 ))<=$(=max(week(day,0)))"}>} TOTAL <Dimentions> facts) *

          Sum({$<week= {">=$min(week(day,0) -5 ))<=$(=max(week(day,0)))"}>} TOTAL <Week> facts))

          , Dimentions, Week))

            • Re: Calculate a grand, row, column total
              Jose Hoyos

              ah thank you for the fast reply..

               

              I get an output however when i select only one week then it gives me 100% as result.

              And i think its only calculating the values from the selected periodes and not going 'back' to a set of max 5 weeks.

              I hope this visual helps with the expected output:

              Index.PNG

              gr

                • Re: Calculate a grand, row, column total
                  Sunny Talwar

                  How about if you do this

                   

                  Sum({$<week= {">=$min(week(day,0) -5 ))<=$(=max(week(day,0)))"}>} Aggr(

                  (Sum({$<week= {">=$min(week(day,0) -5 ))<=$(=max(week(day,0)))"}>} facts) *

                  Sum({$<week= {">=$min(week(day,0) -5 ))<=$(=max(week(day,0)))"}>} TOTAL facts))

                  /

                  (Sum({$<week= {">=$min(week(day,0) -5 ))<=$(=max(week(day,0)))"}>} TOTAL <Dimentions> facts) *

                  Sum({$<week= {">=$min(week(day,0) -5 ))<=$(=max(week(day,0)))"}>} TOTAL <Week> facts))

                  , Dimentions, Week))