If Statement/Set Analysis

[cbaqir]

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

[sunny_talwar]

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

[Kushal_Chawda]

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