topic Re: If Statement/Set Analysis in QlikView

If Statement/Set Analysis

cbaqir — Wed, 05 Aug 2015 18:16:56 GMT

I currently use the following expression to display red/yellow/green/gray symbols for a health chart:

=if([SUB_OVERALL_HEALTH] = 'Green' , 'qmem://<bundled>/BuiltIn/check_g.png'

,if([SUB_OVERALL_HEALTH] = 'Yellow' , 'qmem://<bundled>/BuiltIn/exclamation_y.png'

,if([SUB_OVERALL_HEALTH] = 'Red' , 'qmem://<bundled>/BuiltIn/cross_r.png'

, 'qmem://<bundled>/BuiltIn/minus.png'

)))

I need to include some filters so that certain rows of data are not displayed. For example, I only want to see info for a REQUEST_ID that meets the following conditions:

1. REQ_STATUS = 'Open'

2. DISPLAY_STATUS <> 'Close Out'

3. REQ_TYPE <> Minor Project, Ops and Maint or Admin

How do I incorporate this (I figured it would be set analysis) into the chart so I only see data for REQUEST_IDs that meet those conditions?

Thanks,

Cassandra

Re: If Statement/Set Analysis

Kushal_Chawda — Wed, 05 Aug 2015 18:27:49 GMT

Your set analysis will look like this

=sum({<REQ_STATUS={'Open'},DISPLAY_STATUS-={'Close Out'},REQ_TYPE-={' Minor Project','Ops and Maint or Admin'}>}Values)

Re: If Statement/Set Analysis

Kushal_Chawda — Wed, 05 Aug 2015 18:31:30 GMT

Create chart

Dimension- REQUEST_ID and

Expression - =sum({<REQ_STATUS={'Open'},DISPLAY_STATUS-={'Close Out'},REQ_TYPE-={' Minor Project','Ops and Maint or Admin'}>}Values)

Re: If Statement/Set Analysis

CELAMBARASAN — Wed, 05 Aug 2015 18:33:46 GMT

You could try the below Expr

=If(Count({<REQ_STATUS={'Open'}>-<DISPLAY_STATUS={'Close Out'},REQ_TYPE={' Minor Project','Ops and Maint or Admin'}>} RequestID), if([SUB_OVERALL_HEALTH] = 'Green' , 'qmem://<bundled>/BuiltIn/check_g.png'

,if([SUB_OVERALL_HEALTH] = 'Yellow' , 'qmem://<bundled>/BuiltIn/exclamation_y.png'

,if([SUB_OVERALL_HEALTH] = 'Red' , 'qmem://<bundled>/BuiltIn/cross_r.png'

, 'qmem://<bundled>/BuiltIn/minus.png'

))))

Re: If Statement/Set Analysis

cbaqir — Wed, 05 Aug 2015 19:17:46 GMT

Not sure why I would have a sum function?

Re: If Statement/Set Analysis

cbaqir — Wed, 05 Aug 2015 19:37:58 GMT

I'm not counting or summing, just displaying the data.

Re: If Statement/Set Analysis

cbaqir — Wed, 05 Aug 2015 19:51:31 GMT

Here is a sample of the chart.

Re: If Statement/Set Analysis

elyM — Wed, 05 Aug 2015 21:37:28 GMT

Hi Cassandra,

this is your solution.

Regards,

Ely Malki

Re: If Statement/Set Analysis

maxgro — Wed, 05 Aug 2015 21:59:01 GMT

add another if to all your expressions

if(REQ_STATUS = 'Open' and DISPLAY_STATUS <> 'Close Out' and not match(REQ_TYPE, 'Minor Project', 'Ops and Maint or Admin'),

if([SUB_OVERALL_HEALTH] = 'Green' , 'qmem://<bundled>/BuiltIn/check_g.png'

,if([SUB_OVERALL_HEALTH] = 'Yellow', 'qmem://<bundled>/BuiltIn/exclamation_y.png'

,if([SUB_OVERALL_HEALTH] = 'Red', 'qmem://<bundled>/BuiltIn/cross_r.png'

, 'qmem://<bundled>/BuiltIn/minus.png'

))),

null()

)

Re: If Statement/Set Analysis

antoniotiman — Wed, 05 Aug 2015 21:59:18 GMT

In Dimensions.

e.g instead of Dimension REQ_STATUS

IF(REQ_STATUS='Open',REQ_STATUS)

IF(DISPLAY_STATUS <> 'Close Out',DISPLAY_STATUS) instead of DISPLAY_STATUS

etc.

Tick Hide When Values is NULL in Dimensions Tab

Re: If Statement/Set Analysis

sunny_talwar — Wed, 05 Aug 2015 23:20:55 GMT

May be this:

=If(Only({<REQ_STATUS = {'Open'}, DISPLAY_STATUS -= {'Close Out'}, REQ_TYPE -= {'Minor Project', 'Ops and Maint', 'Admin'}>}[SUB_OVERALL_HEALTH]) = 'Green' , 'qmem://<bundled>/BuiltIn/check_g.png',

If(Only({<REQ_STATUS = {'Open'}, DISPLAY_STATUS -= {'Close Out'}, REQ_TYPE -= {'Minor Project', 'Ops and Maint', 'Admin'}>}[SUB_OVERALL_HEALTH]) = 'Yellow' , 'qmem://<bundled>/BuiltIn/exclamation_y.png',

If(Only({<REQ_STATUS = {'Open'}, DISPLAY_STATUS -= {'Close Out'}, REQ_TYPE -= {'Minor Project', 'Ops and Maint', 'Admin'}>}[SUB_OVERALL_HEALTH]) = 'Red' , 'qmem://<bundled>/BuiltIn/cross_r.png',

'qmem://<bundled>/BuiltIn/minus.png')))

Re: If Statement/Set Analysis

Kushal_Chawda — Thu, 06 Aug 2015 06:32:14 GMT

Create Calculated Dimension for Request ID,

=aggr(if(count({<REQ_STATUS={'Open'},DISPLAY_STATUS-={'Close Out'},REQ_TYPE-={' Minor Project','Ops and Maint or Admin'}>}REQUEST_ID)>0,

REQUEST_ID),REQUEST_ID)

And check Suppress when value is NULL