topic Re: how to write max yoy value expression in a textbox in QlikView

how to write max yoy value expression in a textbox

kunkumnaveen — Tue, 06 Jun 2017 09:48:43 GMT

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

avinashelite — Tue, 06 Jun 2017 09:53:21 GMT

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

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

kunkumnaveen — Tue, 06 Jun 2017 09:54:48 GMT

any alternative expression to get max yoy value

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

RonaldDoes — Tue, 06 Jun 2017 09:56:33 GMT

Hi Naveen,

Can you share your QVW?

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

kunkumnaveen — Tue, 06 Jun 2017 09:59:51 GMT

PFA qvw file

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

kunkumnaveen — Tue, 06 Jun 2017 10:00:28 GMT

i have attach the file

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

kunkumnaveen — Tue, 06 Jun 2017 10:05:41 GMT

Any help in writing the epression plz

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

prma7799 — Tue, 06 Jun 2017 10:22:27 GMT

Please try like this in your text object

=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

avinashelite — Tue, 06 Jun 2017 10:27:32 GMT

check the attachment

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

kunkumnaveen — Tue, 06 Jun 2017 10:42:40 GMT

Thanks for your reply ,

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

prma7799 — Tue, 06 Jun 2017 10:46:32 GMT

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

kunkumnaveen — Tue, 06 Jun 2017 10:53:38 GMT

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

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

prma7799 — Tue, 06 Jun 2017 10:57:04 GMT

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

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

sunny_talwar — Tue, 06 Jun 2017 11:03:04 GMT

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

kunkumnaveen — Tue, 06 Jun 2017 11:04:08 GMT

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

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

tresB — Tue, 06 Jun 2017 11:09:17 GMT

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

kunkumnaveen — Tue, 06 Jun 2017 11:10:41 GMT

Hello Sunny,

i am working on Qliksense 3.0

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

sunny_talwar — Tue, 06 Jun 2017 11:11:07 GMT

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

kunkumnaveen — Tue, 06 Jun 2017 11:11:33 GMT

i am using Qliksense

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

sunny_talwar — Tue, 06 Jun 2017 11:12:19 GMT

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