using IF condition with SUM

[#1]

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

Thanks

[sunny_talwar · Accepted solution]

Try this may be:

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

[#3]

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

[sunny_talwar]

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

[#5]

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

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

[#6]

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

[#7]

try

Sum( { $<Postcode = {"<=Max_PC"}, Postcode = {">=Min_PC"}> } [Total Lodge Vol] )

[#8]

Thanks