22 Replies Latest reply: Jun 6, 2017 7:24 AM by P M

# how to write max yoy value expression in a textbox

How All,

I have to display max YOY increase value

to get max YOY increase value which is (67%) in 2016 i wrote below expression in text box ,but i am getting -0 value

Expression:  =num(Max(Aggr((Sum(value)-Above(Sum(value)))/Above(Sum(value)), year)),'###0')

some thing is wrong with this expression

can any suggestion plz

thanks

• ###### Re: how to write max yoy value expression in a textbox

Above doesn't work in the text box ..that is the issue

• ###### Re: how to write max yoy value expression in a textbox

any alternative expression to get max yoy value

• ###### Re: how to write max yoy value expression in a textbox

Hi Naveen,

• ###### Re: how to write max yoy value expression in a textbox

PFA qvw file

• ###### Re: how to write max yoy value expression in a textbox

Do you have QV12 by any chance? If you do, you can use The sortable Aggr function is finally here! like this

=Num(Max(Aggr((Sum(value)-Above(Sum(value)))/Above(Sum(value)), (year, (NUMERIC)))),'###0.00%')

• ###### Re: how to write max yoy value expression in a textbox

Hello Sunny,

i am working on Qliksense 3.0

• ###### Re: how to write max yoy value expression in a textbox

Then this should work for you without any issues

=Num(Max(Aggr((Sum(value)-Above(Sum(value)))/Above(Sum(value)), (year, (NUMERIC)))),'###0.00%')

• ###### Re: how to write max yoy value expression in a textbox

i have attach the file

• ###### Re: how to write max yoy value expression in a textbox

check the attachment

• ###### Re: how to write max yoy value expression in a textbox

i wont be able to copy you Qvw file in to my server due to some security reason ...could you plz send expression

• ###### Re: how to write max yoy value expression in a textbox

Any help in writing the epression plz

• ###### Re: how to write max yoy value expression in a textbox

=Num(( sum({<year = {\$(=max(year))} >}value) - sum({<year = {\$(=max(year)-1)} >}value)) / (sum({<year = {\$(=max(year)-1)} >}value))*100,'#.##')

• ###### Re: how to write max yoy value expression in a textbox

it looks like ,that your expression  will alway take max year value only ,for the above example it gives exact output

but  in case if  YOY  value of 2015 is more then  2016 value still your expression will show 2016 value even though it has to be 2015 value ....

• ###### Re: how to write max yoy value expression in a textbox

It will show according to your values

like if 2015 values 1200 and 2016 value 1000 then  is shows you - 50%.

• ###### Re: how to write max yoy value expression in a textbox

Exactly , that is what i mean ,i was asked to show max value in the chart irrespective of years,

what i mean is if 2015 value more then 2016 value then i was told to display 2015 value

or

if 2016 value is greater then 2015 value then i need to display 2016 value ......

i known it doesn't make sense ,but what we can do client is looking so

• ###### Re: how to write max yoy value expression in a textbox

You want value or YOY please specify clearly .... or share some expected output.

• ###### Re: how to write max yoy value expression in a textbox

sorry my apologies ,i mean to say YOY value  which is in percentage

• ###### Re: how to write max yoy value expression in a textbox

Like this?

=if( (sum({<year = {\$(=max(year)-1)} >}value)) > sum(value)

,

(Num(( sum({<year = {\$(=max(year)-1)} >}value) - sum({<year = {\$(=max(year)-2)} >}value)) / (sum({<year = {\$(=max(year)-2)} >}value)),'#,##0%'))

,if(sum(value) >  (sum({<year = {\$(=max(year))} >}value)) ,((Num(( sum({<year = {\$(=max(year))} >}value) - sum({<year = {\$(=max(year)-1)} >}value)) / (sum({<year = {\$(=max(year)-1)} >}value)),'#,##0%')))

))

• ###### Re: how to write max yoy value expression in a textbox

naveen kumar wrote:

i known it doesn't make sense ,but what we can do client is looking so

But I don't really agree. It makes complete sense to me. Business can always ask to see the maximum figure sold across years. You just have to use the right expression. Using QV 12 + or Qlik sense 2.2+ you can try like:

=Num(Max(Aggr((Sum(value)-Above(Sum(value)))/Above(Sum(value)), (year,(NUMERIC, ASCENDING)))), '#,##0%')

Now with qv 12, you can use sorting within aggr() itself.

• ###### Re: how to write max yoy value expression in a textbox

i am using Qliksense

• ###### Re: how to write max yoy value expression in a textbox

Naveen, like I mentioned, this will work in Qlik Sense

• ###### Re: how to write max yoy value expression in a textbox

Qlik Sense 2.2 onwards it is there.