Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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

Tags (1)
1 Solution

Accepted Solutions
Employee
Employee

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

7 Replies
Not applicable

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)

Employee
Employee

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:


Capture.PNG

Not applicable

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.

Not applicable

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

try =max $(YTDVar)

the dollar sign expansion should get you the value

Employee
Employee

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

Not applicable

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

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

Not applicable

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!

Community Browser