Can't find the max/highest number displayed in YTD

[#1]

I need help finding the highest number displayed in the 'YTD Var' column of an object.

The dimension = Brand

The expression = =((Sum($(vSetYTD)Sales))-(Sum($(vSetPreviousYearYTD)Sales)))
The expression label = ='YTD Var'

For example, if the values were 900, 400, and 450, I need to see the 900 in a text box because it is the max/highest value

Thanks!!!!

[#2]

I would recommend defining your expression as a variable (and you may be doing that) as 'YTDVar'.

Once that is done, in your testbox, you should be able to  have the following expression

=max(YTDVar)

[JonnyPoole]

I think you would need to grab the max of the aggregated values.  You can try:

max(

     aggr(

          ((Sum($(vSetYTD)Sales))-(Sum($(vSetPreviousYearYTD)Sales)))

     ,<Dimension>)

)

like this:

[#4]

I defined a new variable for YTD Var, but the =max(vYTDVar) isn't calculating. The text box shows a '-' in it.

[#5]

try =max $(YTDVar)

the dollar sign expansion should get you the value

[JonnyPoole · Accepted solution]

I noticed that if you replace vYTDVar with your expression you get

max (

((Sum($(vSetYTD)Sales))-(Sum($(vSetPreviousYearYTD)Sales)))

)

Max(sum())  is invalid qlik syntax whether its in a variable or not.  You would need an aggr() in the middle to define the level of aggregation for the sum before it picks out the maximum of thos pre-aggregated values. No way around it.

So the basic structure is:  Max( aggr(  sum() , <dimension> )  )  .

[#7]

The error message I receive is 'error in expression: nested aggregation not allowed'

[#8]

Thank you!

Here's what worked: =Max(aggr($(vYTDVar) , [Brand Name] )  ) 

The returned value I get is one of the ones not shown in the straight table (because I have dimension limits set up). But at least I am able to find the max!