topic nested If statement in QlikView

nested If statement

sakshikaul — Thu, 22 Nov 2018 11:17:32 GMT

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

Re: nested If statement

shiveshsingh — Thu, 22 Nov 2018 11:40:11 GMT

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)

Re: nested If statement

sakshikaul — Thu, 22 Nov 2018 11:46:46 GMT

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),

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

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

Re: nested If statement

shiveshsingh — Thu, 22 Nov 2018 11:54:39 GMT

May be this?

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

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

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

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

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

Re: nested If statement

sakshikaul — Thu, 22 Nov 2018 12:02:24 GMT

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

Re: nested If statement

shiveshsingh — Thu, 22 Nov 2018 12:13:10 GMT

Plz try this.

You need to handles null for this..

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

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

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

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

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

If above doesn't work, try below one also

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

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

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

/
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())))

Re: nested If statement

sakshikaul — Fri, 23 Nov 2018 04:02:21 GMT

Above expression is not giving correct result

below expression is giving an error

Re: nested If statement

shiveshsingh — Fri, 23 Nov 2018 04:11:09 GMT

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

Re: nested If statement

sakshikaul — Fri, 23 Nov 2018 04:23:10 GMT

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

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

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

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

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

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

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

Re: nested If statement

shiveshsingh — Fri, 23 Nov 2018 04:29:08 GMT

This should work

Re: nested If statement

sakshikaul — Fri, 23 Nov 2018 05:08:49 GMT

yes its working . Thanks