how to output a Text field value in a Max based expression ?

[#1]

I have an expression like below, but (pulled out the set analysis for readability)

Its a simple Burn rate % expression where I want to show the highest and lowest %. This expression works well.

My problem is I need to show the Organization along side this one in a neighboring column.

Max(Aggr(Sum(Amount) / Sum(Planned),Organization,Month,ExpLabel ) )

How would I output the corresponding Text Value of the Organization?

Thanks!

Organizaftion

[vgutkovsky]

Brian,

Try

maxstring(aggr(

    if(sum(Amount)/sum(Planned)

    =

    max(total <Month,ExpLabel> aggr(sum(Amount)/sum(Planned),Organization,Month,ExpLabel)),

        Organization

    ),

    Organization,Month,ExpLabel

)

Regards,

Vlad

[#3]

It doesn't quite give me the correct answer but does output an Org. which is Great.

I attached an Excel table to original post that should help with that. Its hsows the correct results Im trying to get.

Not sure hwo the TOTAL helps but it only works when its used.

[vgutkovsky]

I think you forgot to attach...also a screenshot of your chart would be helpful.

[vgutkovsky · Accepted solution]

See attached. What I pasted above should have worked too, but I guess the evaluation was too heavy. Taking part of it to variables helped.

Regards,

Vlad

[#6]

Awesome Job. Exactly what I needed.  Variables are very useful in situations like this Im learning.

You would think there be more postings like this with a dual output but I guess each case is unique?

Thanks!

[#7]

I keep getting XYZ at 100% for Min and nothing for max.; Is there any thign wrong syntax wise?

Here is my actual script: I use them to generate the excel table I attached.

Script Variables;

SET Actual = Sum({1<[Cost Type]={"Actual*"}>}Amount);

SET Overburn = Max(Aggr($(Actual) / Sum({1<[Cost Type]={"Projected*"}>}Amount),Organization,Month,ExpLabel ) );

SET Underburn = Min(Aggr( If($(Actual) >0, $(Actual) ) / Sum({1<[Cost Type]={"Projected*"}>}Amount),Organization,Month,ExpLabel ) ) ;

Expression:

if(ExKey='EX6',
maxstring(aggr(if($(Actual) / Sum({1<[Cost Type]={"Projected*"}>}Amount) = $(Overburn),Organization)
,Organization,Month,ExpLabel
)),

Maxstring(aggr(if($(Actual) / Sum({1<[Cost Type]={"Projected*"}>}Amount) = $(Underburn),Organization)
,Organization,Month,ExpLabel
))
)

[vgutkovsky]

Yes, your vOverburn and vUnderburn variables need to start with an '=' and the check against these variables in the aggr should not use $() expansion. See the example I attached above.

Vlad

[#9]

Yep, I guess I need to understand when to use $(Expansion) and when not too.

That really isnt explained in the help guide?

Thanks so much!

[vgutkovsky]

The simple rule is that $() should be used when you want the variable to evaluate. It's typically used when you are plugging the variable as the chart expression, so that it will be evaluated for each dimension. By contrast, if your variable starts with '=' then it will be evaluated once for the entire application and will ignore chart dimensions if you plug it into a chart. In that case, you would also not use variable expansion.

Regards,

Vlad