Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

using IF condition with SUM

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

1 Solution

Accepted Solutions

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)

7 Replies

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)

Not applicable

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?

Not applicable

Re: using IF condition with SUM

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

Not applicable

Re: using IF condition with SUM

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

Not applicable

Re: using IF condition with SUM

try

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

Not applicable

Re: using IF condition with SUM

Thanks

Community Browser