topic Re: range in QlikView

range

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

Hi all,

i want to show the bg colour based on below range

Which ever code is less than 97% or >103% those should bg color red

With in that green range ( 97% -103%) -- stright table

[ i need the rate column values should show in % like 100.00%]

Re: range

Anonymous — Wed, 31 Oct 2018 07:28:48 GMT

to Show the rate in % you may define the Format under tab Settings/Document properties/number

but rate will Show as 10000%

I wopuld prefer to define rate as Expression, there you can define only(rate/100) and under tab number you may format as percent

for Background colour ypu may define background Color

I would not Color all values. only those rates which i want Attention for user should be colored

(but some customer want all values colored)

Re: range

soniasweety — Wed, 31 Oct 2018 07:39:21 GMT

thanks for your reply.. but if i add as expression rate/100 its showing only some values as percentage rest it is showing null, but for that id's i have rate value.

Re: range

HirisH_V7 — Wed, 31 Oct 2018 07:44:00 GMT

Please check using below expression in, Background color.

=IF(Num(rate/100,'#,##0.00%')>'97.00%' and Num(rate/100,'#,##0.00%')<'103.00%',lightGreen(),lightREd())

PFA

HTH

Hirish

Re: range

Frank_Hartmann — Wed, 31 Oct 2018 07:47:02 GMT

maybe like attached

hope this helps

Re: range

soniasweety — Wed, 31 Oct 2018 07:50:40 GMT

Oh you are done on code column i need in rate column .. i will manage that,

but one more thing here.. i want to show two KPI text objects with that green count and red count how can i do that?

Re: range

soniasweety — Wed, 31 Oct 2018 07:52:09 GMT

yes its looks good. but i need to change the expression like below

and i need to convert the rate in script only.

=IF(Num(rate,'#,##0.00%')>'97.00%' and Num(rate,'#,##0.00%')<'103.00%',lightGreen(),lightREd())

Re: range

andrey_krylov — Wed, 31 Oct 2018 08:09:03 GMT

Like this?

Re: range

Frank_Hartmann — Wed, 31 Oct 2018 08:14:29 GMT

textbox1(red):

=sum(aggr(sum(if(rate>103 or rate < 97,1,0)),code))

textbox2(green):

=sum(aggr(sum(if(rate>103 or rate < 97,0,1)),code))

Re: range

soniasweety — Wed, 31 Oct 2018 08:15:32 GMT

its looks exactly what i need ,.. but if you export the table im getting the wrong count for green .. red it is showing correct

how come it is like that? did i miss anything?

Re: range

andrey_krylov — Wed, 31 Oct 2018 08:21:21 GMT

Yes, I should to aggregate the count as Frank did. My exp counts the table rows, not chart

Re: range

soniasweety — Wed, 31 Oct 2018 08:30:17 GMT

hi franky_h79 if i export the table why it is showing only 137 green ? instead of 164?

Re: range

andrey_krylov — Wed, 31 Oct 2018 08:48:40 GMT

Sony, if you add RowNo() to the load script and show this field in the chart then there will be 175 rows instead of 137

test:

LOAD code,

rate,

amount,

RowNo() as RowNo

FROM

color.xlsx

(ooxml, embedded labels, table is Document_TB02);

Re: range

Frank_Hartmann — Wed, 31 Oct 2018 08:49:40 GMT

see attached.

hope this helps

Re: range

soniasweety — Wed, 31 Oct 2018 09:13:03 GMT

both solutions working fine... once i check with real data i will close this thanks.

Re: range

soniasweety — Fri, 02 Nov 2018 12:24:35 GMT

Hi franky_h79

andrey.krylov

im not able to see the red count ... what is the issue .PFA

Re: range

Frank_Hartmann — Fri, 02 Nov 2018 12:32:19 GMT

see attached.

by the way what about the discussion from yesterday? did you find the solution, because you didnt respond to my suggestion?

still facing issue-Need urgent help

Re: range

soniasweety — Fri, 02 Nov 2018 12:36:17 GMT

i have overall count is 169

but in your attached file red count showing5 correct but green count is showing 169 it is wrong it should show 164

export the excel and see the count,

-- yesterday issue not tested because in Production we are not doing any changes till 12 th of this month

Re: range

Frank_Hartmann — Fri, 02 Nov 2018 12:40:30 GMT

see attached

Re: range

soniasweety — Fri, 02 Nov 2018 12:41:09 GMT

i have changed the expression it is working thank you