nested If statement

[sakshikaul]

I want the values which are blank  under sell through column should have no color instead of orange color. Following is the image circled below 

[shiveshsingh]

Are you using dimensionality() fucntion in expression?

If yes, please check in expression background color expression and remove the orange color coding.

Let me know if you are facing any issues in changing the expression ( Color)

[sakshikaul]

Here I am using in expression --> background color

=if(num(
(sum( {stateperfB<Sold_Date = {">=$(sstart)<=$(eend)"},[Season Code] = {$(MasterCurrentSeason)} >} quantity_sold)- sum( {stateperA< Sold_Date = {">=$(sstart)<=$(eend)"}, [Season Code] = {$(MasterCurrentSeason)}>} quantity_returns))

/
sum( {stateperfB<[Season Code] = {$(MasterCurrentSeason)} >} quantity_received)
,'#,##0%')>='60%',Green(),

if(num(
(sum( {stateperfB<Sold_Date = {">=$(sstart)<=$(eend)"},[Season Code] = {$(MasterCurrentSeason)} >} quantity_sold)- sum( {stateperA< Sold_Date = {">=$(sstart)<=$(eend)"}, [Season Code] = {$(MasterCurrentSeason)}>} quantity_returns))

/

sum( {stateperfB<[Season Code] = {$(MasterCurrentSeason)} >} quantity_received)
,

'#,##0%')<'40%',RGB (255,0,0),

if(num(
(sum( {stateperfB<Sold_Date = {">=$(sstart)<=$(eend)"},[Season Code] = {$(MasterCurrentSeason)} >} quantity_sold)- sum( {stateperA< Sold_Date = {">=$(sstart)<=$(eend)"}, [Season Code] = {$(MasterCurrentSeason)}>} quantity_returns))

/
sum( {stateperfB<[Season Code] = {$(MasterCurrentSeason)} >} quantity_received)
,

'#,##0%')='-%',white(),RGB(255,128,0))))

[shiveshsingh]

May be this?

=if(num(
(sum( {stateperfB<Sold_Date = {">=$(sstart)<=$(eend)"},[Season Code] = {$(MasterCurrentSeason)} >} quantity_sold)- sum( {stateperA< Sold_Date = {">=$(sstart)<=$(eend)"}, [Season Code] = {$(MasterCurrentSeason)}>} quantity_returns))

/
sum( {stateperfB<[Season Code] = {$(MasterCurrentSeason)} >} quantity_received)
,'#,##0%')>='60%',Green(),

if(num(
(sum( {stateperfB<Sold_Date = {">=$(sstart)<=$(eend)"},[Season Code] = {$(MasterCurrentSeason)} >} quantity_sold)- sum( {stateperA< Sold_Date = {">=$(sstart)<=$(eend)"}, [Season Code] = {$(MasterCurrentSeason)}>} quantity_returns))

/

sum( {stateperfB<[Season Code] = {$(MasterCurrentSeason)} >} quantity_received)
,

'#,##0%')<'40%',RGB (255,0,0),

if(num(
(sum( {stateperfB<Sold_Date = {">=$(sstart)<=$(eend)"},[Season Code] = {$(MasterCurrentSeason)} >} quantity_sold)- sum( {stateperA< Sold_Date = {">=$(sstart)<=$(eend)"}, [Season Code] = {$(MasterCurrentSeason)}>} quantity_returns))

/
sum( {stateperfB<[Season Code] = {$(MasterCurrentSeason)} >} quantity_received)
,

'#,##0%')='-%',RGB(255,128,0),white())))

[sakshikaul]

still getting the same problem . I want values which are between 40 per and 60 per that should be in orange color and blank values should have no color

[shiveshsingh]

Plz try this. 

You need to handles null for this..

=if(num(
(sum( {stateperfB<Sold_Date = {">=$(sstart)<=$(eend)"},[Season Code] = {$(MasterCurrentSeason)} >} quantity_sold)- sum( {stateperA< Sold_Date = {">=$(sstart)<=$(eend)"}, [Season Code] = {$(MasterCurrentSeason)}>} quantity_returns))

/
sum( {stateperfB<[Season Code] = {$(MasterCurrentSeason)} >} quantity_received)
,'#,##0%')>='60%',Green(),

if(num(
(sum( {stateperfB<Sold_Date = {">=$(sstart)<=$(eend)"},[Season Code] = {$(MasterCurrentSeason)} >} quantity_sold)- sum( {stateperA< Sold_Date = {">=$(sstart)<=$(eend)"}, [Season Code] = {$(MasterCurrentSeason)}>} quantity_returns))

/

sum( {stateperfB<[Season Code] = {$(MasterCurrentSeason)} >} quantity_received)
,

'#,##0%')<'40%',RGB (255,0,0),

if(num(
(sum( {stateperfB<Sold_Date = {">=$(sstart)<=$(eend)"},[Season Code] = {$(MasterCurrentSeason)} >} quantity_sold)- sum( {stateperA< Sold_Date = {">=$(sstart)<=$(eend)"}, [Season Code] = {$(MasterCurrentSeason)}>} quantity_returns))

/
sum( {stateperfB<[Season Code] = {$(MasterCurrentSeason)} >} quantity_received)
,

'#,##0%')='',white(),RGB(255,128,0))))

If above doesn't work, try below one also

=if(num(
(sum( {stateperfB<Sold_Date = {">=$(sstart)<=$(eend)"},[Season Code] = {$(MasterCurrentSeason)} >} quantity_sold)- sum( {stateperA< Sold_Date = {">=$(sstart)<=$(eend)"}, [Season Code] = {$(MasterCurrentSeason)}>} quantity_returns))

/
sum( {stateperfB<[Season Code] = {$(MasterCurrentSeason)} >} quantity_received)
,'#,##0%')>='60%',Green(),

if(num(
(sum( {stateperfB<Sold_Date = {">=$(sstart)<=$(eend)"},[Season Code] = {$(MasterCurrentSeason)} >} quantity_sold)- sum( {stateperA< Sold_Date = {">=$(sstart)<=$(eend)"}, [Season Code] = {$(MasterCurrentSeason)}>} quantity_returns))

/

sum( {stateperfB<[Season Code] = {$(MasterCurrentSeason)} >} quantity_received)
,

'#,##0%')<'40%',RGB (255,0,0),

if(num(
(sum( {stateperfB<Sold_Date = {">=$(sstart)<=$(eend)"},[Season Code] = {$(MasterCurrentSeason)} >} quantity_sold)- sum( {stateperA< Sold_Date = {">=$(sstart)<=$(eend)"}, [Season Code] = {$(MasterCurrentSeason)}>} quantity_returns))

/
sum( {stateperfB<[Season Code] = {$(MasterCurrentSeason)} >} quantity_received)
,

'#,##0%')>'40%' and

num(
(sum( {stateperfB<Sold_Date = {">=$(sstart)<=$(eend)"},[Season Code] = {$(MasterCurrentSeason)} >} quantity_sold)- sum( {stateperA< Sold_Date = {">=$(sstart)<=$(eend)"}, [Season Code] = {$(MasterCurrentSeason)}>} quantity_returns))

/
sum( {stateperfB<[Season Code] = {$(MasterCurrentSeason)} >} quantity_received)
,

'#,##0%')<'60%',,RGB(255,128,0)
,white())))

[sakshikaul]

Above expression is not giving correct result

below expression is giving an error 

[shiveshsingh · Accepted solution]

Can u remove that one comma just before RGB where it is giving error?

[sakshikaul]

=if(num(
(sum( {stateperfB<Sold_Date = {">=$(sstart)<=$(eend)"},[Season Code] = {$(MasterCurrentSeason)} >} quantity_sold)- sum( {stateperA< Sold_Date = {">=$(sstart)<=$(eend)"}, [Season Code] = {$(MasterCurrentSeason)}>} quantity_returns))

/
sum( {stateperfB<[Season Code] = {$(MasterCurrentSeason)} >} quantity_received)
,'#,##0%')>='60%',Green(),

if(num(
(sum( {stateperfB<Sold_Date = {">=$(sstart)<=$(eend)"},[Season Code] = {$(MasterCurrentSeason)} >} quantity_sold)- sum( {stateperA< Sold_Date = {">=$(sstart)<=$(eend)"}, [Season Code] = {$(MasterCurrentSeason)}>} quantity_returns))

/

sum( {stateperfB<[Season Code] = {$(MasterCurrentSeason)} >} quantity_received)
,

'#,##0%')<'40%',RGB (255,0,0),

if(num(
(sum( {stateperfB<Sold_Date = {">=$(sstart)<=$(eend)"},[Season Code] = {$(MasterCurrentSeason)} >} quantity_sold)- sum( {stateperA< Sold_Date = {">=$(sstart)<=$(eend)"}, [Season Code] = {$(MasterCurrentSeason)}>} quantity_returns))

/
sum( {stateperfB<[Season Code] = {$(MasterCurrentSeason)} >} quantity_received)
,

'#,##0%')>='40%' and

num(
(sum( {stateperfB<Sold_Date = {">=$(sstart)<=$(eend)"},[Season Code] = {$(MasterCurrentSeason)} >} quantity_sold)- sum( {stateperA< Sold_Date = {">=$(sstart)<=$(eend)"}, [Season Code] = {$(MasterCurrentSeason)}>} quantity_returns))

/
sum( {stateperfB<[Season Code] = {$(MasterCurrentSeason)} >} quantity_received)
,

'#,##0%')<='60%',RGB(255,128,0))))

[shiveshsingh]

This should work