Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

cbaqirdhds
Contributor II

If Statement/Set Analysis

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

1 Solution

Accepted Solutions
MVP
MVP

Re: If Statement/Set Analysis

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

)

11 Replies

Re: If Statement/Set Analysis

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

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

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'

)))
)

cbaqirdhds
Contributor II

Re: If Statement/Set Analysis

Not sure why I would have a sum function?

cbaqirdhds
Contributor II

Re: If Statement/Set Analysis

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

cbaqirdhds
Contributor II

Re: If Statement/Set Analysis

Here is a sample of the chart.

ely_malki
Contributor

Re: If Statement/Set Analysis

Hi Cassandra,

this is your solution.

Regards,

Ely Malki

MVP
MVP

Re: If Statement/Set Analysis

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
Honored Contributor III

Re: If Statement/Set Analysis

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

Community Browser