topic Re: Set Analysis with multiple conditions in QlikView

Set Analysis with multiple conditions

Wed, 20 Apr 2016 20:32:31 GMT

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

Re: Set Analysis with multiple conditions

sunny_talwar — Wed, 20 Apr 2016 20:58:15 GMT

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?

Re: Set Analysis with multiple conditions

swuehl — Wed, 20 Apr 2016 21:15:11 GMT

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

Re: Set Analysis with multiple conditions

Wed, 20 Apr 2016 21:32:50 GMT

I need to see the max indicator value for each department

Re: Set Analysis with multiple conditions

sunny_talwar — Wed, 20 Apr 2016 21:56:28 GMT

Then may be use as suggested by Stefan

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

Re: Set Analysis with multiple conditions

Thu, 21 Apr 2016 16:00:49 GMT

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"?

Re: Set Analysis with multiple conditions

sunny_talwar — Thu, 21 Apr 2016 16:04:14 GMT

May be this:

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

Re: Set Analysis with multiple conditions

swuehl — Thu, 21 Apr 2016 21:38:57 GMT

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.