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

using IF condition with SUM

Hi all

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

• Re: using IF condition with SUM

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

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

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

• Re: using IF condition with SUM

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

• Re: using IF condition with SUM

Hi Sunny,

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