7 Replies Latest reply: Jun 29, 2016 9:27 PM by Preet kaur RSS

    using IF condition with SUM

    michael tamsil

      Hi all

       

      continue from https://community.qlik.com/thread/218060

      Question 1

      can someone explain below this expression base on the link above

      Sum({<SAP = {" = Count( Distinct Supplier) = Count (Total Distinct Supplier) "} > } Value)



      if i combine with if  Count( distinct supplier) = Count (Total Distinct supplier) where  should not null

       

      if ( Count( distinct supplier) = Count (Total Distinct supplier),
      Sum({<SAP = {" = Count( Distinct Supplier) = Count (Total Distinct Supplier) "} > } Value), 0)

       

      Question 2

      is it possible using "if" condition like above? because i've problem where "Dimension SAP Formula" is null, then the "Measure Value" show the value and not 0

       

      picture.png

       

      Thanks

        • Re: using IF condition with SUM
          Sunny Talwar

          Try this may be:

           

          Sum({<SAP = {"=Count(Distinct Supplier) = Count (Total Distinct Supplier) and Len(Trim(Count(Distinct Supplier))) > 0"} > } Value)

            • Re: using IF condition with SUM
              michael tamsil

              i've already tried solution you give, but not quite right ...

              and already tried by myself (try to figure out)... not work either...

               

              thanks Sunny for replay

                • Re: using IF condition with SUM
                  Sunny Talwar

                  Would you be able to share a sample to look at?

                    • Re: using IF condition with SUM
                      michael tamsil

                      i've already add qvf file.

                       

                      sheet "table" is the list

                      sheet "List SAP like to Like " is only test for "variable" that i've added

                      sheet "IF condition with SUM" is the real problem and the solution


                      on the last sheet (sheet "IF condition with SUM")... the problem before was "value before" column on the last (the 9,021,761 the red box) ... it should be zero (the green box) is the right

                       

                      table.png

                       

                      the expression each of them

                      "value before" column

                      Sum({<PricingState = {"Submission"}, IsGrandSummary = {"1"},  IsSimulated = {"False"},IsSelectedSKU = {"True"}, PricingStatus = {"Submitted"},SAP = {"=Count( Distinct Supplier ) = Count( Total Distinct Supplier )"}>} Value)

                       

                       

                      "value after" column

                      Sum({<PricingState = {"Submission"}, IsGrandSummary = {"1"},  IsSimulated = {"False"},IsSelectedSKU = {"True"}, PricingStatus = {"Submitted"},SAP = {"=Count( {<PricingState = {'Submission'}, IsGrandSummary = {'1'},  IsSimulated = {'False'},IsSelectedSKU = {'True'}, PricingStatus = {'Submitted'} >} Distinct Supplier ) =

                      Count( {<PricingState = {'Submission'}, IsGrandSummary = {'1'},  IsSimulated = {'False'},IsSelectedSKU = {'True'}, PricingStatus = {'Submitted'} >} Total Distinct Supplier )" } >} Value)

                       

                      it's means that i've should add the condition too (the bold type)...

                       

                       

                       

                      i forgot to use the condition at first when submit question to community, ...sorry...

                       

                       

                       

                      Thanks Sunny for your time...  it's give me another ways to solve

                  • Re: using IF condition with SUM
                    Preet kaur

                    Hi Sunny,

                     

                    Could you please help me out with this?

                    Sum(

                    {

                      $<Postcode= if(Postcode<=Max_PC and Postcode>=Min_PC, Postcode)>

                    }

                    [Total Lodge Vol]

                    )

                    there is something wrong. cant figureout what. Need help.

                    Max_PC, Min_PC are variables

                    Postcode and [Total Lodge Vol] are fields


                    Please help