7 Replies Latest reply: Oct 5, 2015 10:30 AM by Matthew Berry

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

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

• ###### Re: Can't find the max/highest number displayed in YTD

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)

• ###### Re: Can't find the max/highest number displayed in YTD

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

• ###### Re: Can't find the max/highest number displayed in YTD

try =max \$(YTDVar)

the dollar sign expansion should get you the value

• ###### Re: Can't find the max/highest number displayed in YTD

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

• ###### Re: Can't find the max/highest number displayed in YTD

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

• ###### Re: Can't find the max/highest number displayed in YTD

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!

• ###### Re: Can't find the max/highest number displayed in YTD

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: