topic Re: Over %100 format in QlikView

Over %100 format

asknyldz — Wed, 25 Nov 2020 16:16:04 GMT

Hi there,

How can i format over %100 to +%100

%150 should be +%100

Regards

Re: Over %100 format

arvind_patil — Fri, 14 Jul 2017 06:52:19 GMT

Hi Askin,

Please explain more in deep.

Thanks,

Arvind Patil

Re: Over %100 format

zhadrakas — Fri, 14 Jul 2017 06:53:11 GMT

try this

='+' & num(max(Value), '0%')

regards

Re: Over %100 format

Anil_Babu_Samineni — Fri, 14 Jul 2017 07:01:44 GMT

What if you use some thing like below

= '+' & Expression

Re: Over %100 format

Frank_Hartmann — Fri, 14 Jul 2017 07:10:24 GMT

Not sure if i understood you correctly but maybe like that:

if(Value>100,'+%'&100,'%'&Value)

Re: Over %100 format

asknyldz — Fri, 14 Jul 2017 08:21:10 GMT

Dear All,

Thanks for replies, I didn't success.

I want to explain more.

This is the my Expression. This formula result is showing 1500.0%

I want to show is 100.0%+

If result under 100.0%, it can be show original value, but when result is over 100.0%, i want to show 100.0%+

num(((if(CURTYPE='All in USD',sum({<TYPE={'CY'}>}PREMIUM),sum({<TYPE={'CY'}>}PREMIUM_TL))/if(CURTYPE='All in USD',sum({<TYPE={'PY'}>}PREMIUM),sum({<TYPE={'PY'}>}PREMIUM_TL)))-1),'#,##0.0%')

Regards,

Re: Over %100 format

Frank_Hartmann — Fri, 14 Jul 2017 08:29:09 GMT

Maybe like this:

if(num(((if(CURTYPE='All in USD',sum({<TYPE={'CY'}>}PREMIUM),sum({<TYPE={'CY'}>}PREMIUM_TL))/if(CURTYPE='All in USD',sum({<TYPE={'PY'}>}PREMIUM),sum({<TYPE={'PY'}>}PREMIUM_TL)))-1),'#,##0.0%')>1,1,num(((if(CURTYPE='All in USD',sum({<TYPE={'CY'}>}PREMIUM),sum({<TYPE={'CY'}>}PREMIUM_TL))/if(CURTYPE='All in USD',sum({<TYPE={'PY'}>}PREMIUM),sum({<TYPE={'PY'}>}PREMIUM_TL)))-1),'#,##0.0%'))

Re: Over %100 format

asknyldz — Fri, 14 Jul 2017 08:40:51 GMT

Thanks Frank,

I changed as below but now showing +100%+

I see double + icon front and end. Very interesting. I want to see only end

if(num(((if(CURTYPE='All in USD',sum({<TYPE={'CY'}>}PREMIUM),sum({<TYPE={'CY'}>}PREMIUM_TL))/if(CURTYPE='All in USD',sum({<TYPE={'PY'}>}PREMIUM),sum({<TYPE={'PY'}>}PREMIUM_TL)))-1),'#,##0.0%')>1,num(100,'#,##0%'&'+'),num(((if(CURTYPE='All in USD',sum({<TYPE={'CY'}>}PREMIUM),sum({<TYPE={'CY'}>}PREMIUM_TL))/if(CURTYPE='All in USD',sum({<TYPE={'PY'}>}PREMIUM),sum({<TYPE={'PY'}>}PREMIUM_TL)))-1),'#,##0.0%'))

Re: Over %100 format

Frank_Hartmann — Fri, 14 Jul 2017 08:52:09 GMT

try this:

if(num(((if(CURTYPE='All in USD',sum({<TYPE={'CY'}>}PREMIUM),sum({<TYPE={'CY'}>}PREMIUM_TL))/if(CURTYPE='All in USD',sum({<TYPE={'PY'}>}PREMIUM),sum({<TYPE={'PY'}>}PREMIUM_TL)))-1),'#,##0.0%')>1,right(num(100,'#,##0%'&'+'),5),num(((if(CURTYPE='All in USD',sum({<TYPE={'CY'}>}PREMIUM),sum({<TYPE={'CY'}>}PREMIUM_TL))/if(CURTYPE='All in USD',sum({<TYPE={'PY'}>}PREMIUM),sum({<TYPE={'PY'}>}PREMIUM_TL)))-1),'#,##0.0%'))

Re: Over %100 format

passionate — Fri, 14 Jul 2017 09:25:14 GMT

Hi Askin,

Try using this in your expression:

If(Sum(Profit)/Sum(Sales)*100>100,'+100%',NUM(Sum(Profit)/Sum(Sales)*100,'#.#0')&'%')

Here my expression is: Sum(Profit)/Sum(Sales)

Put your expression accordingly.

Regards,

Pankaj

Re: Over %100 format

asknyldz — Fri, 14 Jul 2017 12:04:26 GMT

That's ok.

Thank you!