Set Analysis with multiple conditions

[#1]

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

[sunny_talwar]

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?

[swuehl]

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

[#4]

I need to see the max indicator value for each department

[sunny_talwar]

Then may be use as suggested by Stefan

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

[#6]

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

[sunny_talwar]

May be this:

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

[swuehl]

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.