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

[Kushal_Chawda]

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)

[Kushal_Chawda]

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)

[CELAMBARASAN]

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'

))))

[cbaqir]

Not sure why I would have a sum function?

[cbaqir]

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

[cbaqir]

Here is a sample of the chart.

[elyM]

Hi Cassandra,

this is your solution.

Regards,

Ely Malki

[maxgro · Accepted solution]

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

)

[antoniotiman]

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