4 Replies Latest reply: Sep 5, 2017 2:39 PM by Nick Jose RSS

    Group By in Qlik Sense Frontend

    Nick Jose

      serj_shu kush141087 petter-s mdmukramali antoniotiman

       

      Hi,

       

      Can somebody help me in calculating the below in QlikSense:

       

      Required Output:

       

      (a) Revised beta Banana = 1597160

      Formula: Beta Banana / Beta Apple * Revised Beta Apple

                       Where Revised Beta Apple = Alpha Apple / Total of Alpha Apple * Total of Beta Apple  


      (b) Revised beta Oranges = 682002

      Formula: Beta Oranges / Beta Apple * Revised Beta Apple

      Where Revised Beta Apple = Alpha Apple / Total of Alpha Apple * Total of Beta Apple


      Data:

                

      alphaActual betaRevised betaRevised alpha
      Type GroupAppleBananaOrangesAppleBananaOrangesAppleBananaOrangesAppleBananaOranges
      aqw             274                 6,010              52,617              478                 6,469              39,064              122 1,654 9,990              122                 2,681              23,475
      cqw             192              22,710              78,688              218              94,018              74,374                 86                  36,943                  29,225                 86              10,132              35,107
      sqw             363              12,689              27,479              346              93,535              28,088              162                  43,781                  13,147              162                 5,661              12,260
      dqw             502                 2,863              80,829              200              37,016              18,845              224                  41,452                  21,103              224                 1,277              36,062
      fqw             740              83,659              80,470                 29              89,794              17,979              330            1,022,263                204,683              330              37,325              35,902
      rqw             164              73,114              59,121              186              81,350              54,295                 73                  32,001                  21,359                 73              32,620              26,377
      eqw             781              72,626              72,624              221              65,614              26,124              348                103,452                  41,189              348              32,402              32,401
      hqw             806              81,479              91,129              130              46,617              56,818              360                128,949                157,166              360              36,352              40,657
      yqw             253                    198              33,320              200              48,297              31,849              113                  27,258                  17,975              113                       88              14,866
      nqw             939              27,615              54,205              229              87,135              90,830              419                159,406                166,166              419              12,320              24,184
                5,014            382,963            630,482           2,237            649,845            438,266           2,237            1,597,160                682,002           2,237            170,859            281,290

       

      The problem is when I calculate (a) I get the value of Beta Banana i.e. 649845. And when I add the dimension ''Type" in Table box and set the property "Totals" as Sum it gives me the perfect value i.e. 1597160.


      But the same expression does not work in other objects like KPI or Text Object.

       

      I think I am not able to properly Group By or something.

       

      What I need is 'Type' and 'Group' wise totals as calculated in the last row of above table. Excel is also attached to understand the working and output.


      Please understand that the attached data is only for one group while I have multiple groups.


      Any quick help would be highly appreciated.

       

      Thanks

      Nick