2 Replies Latest reply: Mar 15, 2016 10:28 PM by VIKRAM ARORA RSS

    Qlik Sense Sum on Group By and Where Condition

    VIKRAM ARORA

      Need help in Qlik Sense for Sum on Group By and Where Condition for percentage calculation. The VW appears only once Group by Tat_Qtr which for percentage calculation needs to be divided by every row of TXN_AMT_USD 

       

      Complete Expression:

      (SUM({$<[Record_Type]={'Bklg','Bill'}>}[TXN_AMT_USD]) / <Need Expression> ) * 100

       

      <Need Expression> for

      Sum of TXN_AMT_USD IF record Type = 'WV' which appears only once for Group By TAT_Qtr

       

       

      Snapshot_DateRecord_TypeProduct Product_DescTAT_YEARTAT_QtrTXN_AMT_USD
      5/4/2014Bklg03-25598-00SSS6203 ELP4 2004DN FOR ABC NA20142014Q4518520
      5/11/2014Bklg03-25598-00SSS6203 ELP4 2004DN FOR ABC NA20142014Q4520550
      5/18/2014Bklg03-25598-00SSS6203 ELP4 2004DN FOR ABC NA20142014Q4474730
      5/25/2014Bklg03-25598-00SSS6203 ELP4 2004DN FOR ABC NA20142014Q4526060
      6/1/2014Bklg03-25598-00SSS6203 ELP4 2004DN FOR ABC NA20142014Q4184440
      6/8/2014Bklg03-25598-00SSS6203 ELP4 2004DN FOR ABC NA20142014Q4527800
      6/15/2014Bklg03-25598-00SSS6203 ELP4 2004DN FOR ABC NA20142014Q4661780
      6/19/2014Bklg03-25598-00SSS6203 ELP4 2004DN FOR ABC NA20142014Q4232290
      6/19/2014WV03-25598-00SSS6203 ELP4 2004DN FOR ABC NA20142014Q42320000
        • Re: Qlik Sense Sum on Group By and Where Condition
          Stefan Wühl

          Maybe like

           

          (

          SUM({$<[Record_Type]={'Bklg','Bill'}>}[TXN_AMT_USD])

          / SUM({$<[Record_Type]={'WV'}>} TOTAL<TAT_Qtr> [TXN_AMT_USD])

          ) * 100

            • Re: Qlik Sense Sum on Group By and Where Condition
              VIKRAM ARORA

              Hi seuehl,

               

              Thanks for your reply. This slightly helps but can you please suggested if nested level are more than one (as TAT_Qtr in this case).

               

              For Example if i need Minimum of dates first on Level1 than on Level2 than on Level3 and a condition that if that date is minimum of Quarter (as Running Total will be zero for that first entry and than next all subsequent entries will be dependent on that output entry) which will be Grouped By say Level1 than on Level2 than on Level3.


              Consider data below for above scenario.


              nic

               

               

                    

              Report FiltersOrg_CodePart_Number

              Org_Code

              Part_NumberCustomer_PartForecast_Dates
              VCNACPM-5002-TR147100375
              8-Feb-1615-Feb-1622-Feb-1629-Feb-167-Mar-16
              Current Fcst 2,254 662 402 402 603
              Internal Orders 0 0 0 10,000 0
              Intransit Shipment Qty 0 0 0 0 0
              Projected On Hand (2,254)(2,916)(3,318)6,280 5,677
              VCNACPM-5004-TR147100360
              8-Feb-1615-Feb-1622-Feb-1629-Feb-167-Mar-16