Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Not applicable

Set Analysis with multiple conditions

Hi,

I need to show the single value for a field called "INDICATOR" even though it repeats across many groups and sub-groups. The set would be built like this:

SELECTED METRIC

SELECTED YEAR

SELECTED PERIOD

FOR EACH DEPARTMENT

WHERE LEVEL="EXECUTIVE"

How can I achieve those different conditions in one statement?

Here's what I tried, but it still shows me the maximum of 5 all departments. Not the indicator PER department. ONLY isn't working...

Max({ < ${METRIC}, ${YEAR}, ${PERIOD} , ${DEPARTMENT} ,[LEVEL]={"EXECUTIVEl"} > }  [INDICATOR])


7 Replies
Highlighted

Re: Set Analysis with multiple conditions

Try this:

Max({<[LEVEL]={'EXECUTIVEl'}>} [INDICATOR])

When you say for each department, do you want to see the max value from each of the departments? or one value from all department combined? Not sure I completely understand, do you have a sample you can share? with expected output?

Highlighted
MVP
MVP

Re: Set Analysis with multiple conditions

If you want to see a measure per department, you could create a chart with dimension DEPARTMENT

and then an appropriate expression.

If you want SELECTED METRIC,SELECTED YEAR,SELECTED PERIOD, you don't need to do anything special, user selections will be considered by default in your aggregations.

Maybe just create your expression like Sunny suggested

=Max({ < [LEVEL]={'EXECUTIVEl'} > }  [INDICATOR])


You could also create a text box with the results displayed in a concatenated string:


=Concat( Aggr( DEPARTMENT & ': ' & Max({ < [LEVEL]={'EXECUTIVEl'} > }  [INDICATOR]), DEPARTMENT), ',')


But also here, you would need to create something with 1 dimension and 1 expression if you want to show your number per DEPARTMENT (which is done withing the aggr() function in latter example).


Dimensions and Measures

Highlighted
Not applicable

Re: Set Analysis with multiple conditions

I need to see the max indicator value for each department

Highlighted

Re: Set Analysis with multiple conditions

Then may be use as suggested by Stefan

Concat(Aggr(Max({<LEVEL = {'EXECUTIVE'}>} INDICATOR), DEPARTMENT), Chr(10))

Highlighted
Not applicable

Re: Set Analysis with multiple conditions

Thank you - we're one step away!

This is returning the indicator value for each department correctly, but it returns it for this level and the other level is null. Can i exclude everything BUT "level-executive"?

Highlighted

Re: Set Analysis with multiple conditions

May be this:

=Concat({<[LEVEL]={'EXECUTIVEl'}>} Aggr(DEPARTMENT & ': ' & Max({<[LEVEL]={'EXECUTIVEl'}>}  [INDICATOR]), DEPARTMENT), ',')

Highlighted
MVP
MVP

Re: Set Analysis with multiple conditions

Not sure which of the two suggested expressions you are referring to. And in which context you are using it.

Maybe what Sunny suggested works, if not, please describe in full detail your expression context:

All dimensions used,

All expressions used.

Chart Type.

I don't ask for some details of your data model for now, but this might be the next step.