topic Re: how to output a Text field value in a Max based expression ? in QlikView

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

Thu, 06 Dec 2012 16:09:06 GMT

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

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

vgutkovsky — Thu, 06 Dec 2012 16:28:59 GMT

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

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

Thu, 06 Dec 2012 22:37:13 GMT

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.

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

vgutkovsky — Thu, 06 Dec 2012 22:47:54 GMT

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

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

vgutkovsky — Thu, 06 Dec 2012 23:17:03 GMT

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

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

Fri, 07 Dec 2012 00:28:21 GMT

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!

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

Fri, 07 Dec 2012 15:12:10 GMT

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

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

vgutkovsky — Fri, 07 Dec 2012 15:45:58 GMT

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

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

Fri, 07 Dec 2012 16:06:47 GMT

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!

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

vgutkovsky — Fri, 07 Dec 2012 17:05:57 GMT

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