topic Re: Modify the logic in QlikView

Modify the logic

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

i want to implement the below logic . into existing logic .

current logic: <97 or >103 is red rest all green but now user want to see 3 colurs.

New logic :

i) <97 or >103--red

100-green

>=97 and <100 or >100 and <=103 --yellow

II) [ these ranges also need as one listbox with values red,green,blue ..so whenever user select that should show in pivot table.]

III) and one more thing is all the above ranges needs to be used as variables. because in future user wants to chnge the colur range we can only modify the variables instead of all expressions.

Note: Attached application is working fine

Re: Modify the logic

kaushiknsolanki — Thu, 22 Nov 2018 05:56:27 GMT

Hi,

Use below expression for color code.

=If(Round([Match Rate],0.0001) <0.97,Red(),
If(Round([Match Rate],0.0001) >=0.97 and Round([Match Rate],0.0001) <1,Yellow(),
If(Round([Match Rate],0.0001) =1,Green(),
If(Round([Match Rate],0.0001) >=1 and Round([Match Rate],0.0001) <1.03,Yellow(),red()))))

You can replace the values with Variable to make it dynamic.

Regards,

Kaushik Solanki

Re: Modify the logic

soniasweety — Thu, 22 Nov 2018 06:07:51 GMT

Hi kaushik_solanki

its look fine . and i need to show that count in text box and listbox right side you can see

how can i do that for green and yellow ,.. red count listbox and text object count is fine because its earlier logic.

Re: Modify the logic

Anil_Babu_Samineni — Thu, 22 Nov 2018 06:27:30 GMT

For Green, You can use

Count({< [Match Rate] = {"=Num([Match Rate],'#,##0.00%')>'97.00%'"}*{"=Num([Match Rate],'#,##0.00%')<'103.00%'"} >} [Business Area Code])

For Yellow, Are you calculating from [Match Rate] ??

Re: Modify the logic

soniasweety — Thu, 22 Nov 2018 06:52:51 GMT

Hi, loveisfail

thanks anil. yes for yellow also using the match rate only.

that same expression i need to show in listbox aswell. how to do that

Re: Modify the logic

soniasweety — Thu, 22 Nov 2018 06:55:51 GMT

No anil its worng.

loveisfail

green count is exact 100.00% then only green colour.

red means <97.00% and >103%

yellow is >=97 and <100 or >100 and <=103

Re: Modify the logic

PradeepReddy — Thu, 22 Nov 2018 07:04:10 GMT

try some thing like this..
create a variables 'vMatchRate _Aggr' with below expression..
aggr(num(Ceil(sum([Net Sales Order US Dollar Amount Yotta])/sum([Net Sales Order US Dollar Amount CDR]),'0.01%'),'#,##0.00%'),RowNo)

Create a 'color' list box with calculated field as below..
IF($(vMatchRate_Aggr) ='100.00%', 'Green',
IF($(vMatchRate_Aggr)< '97.00%' or $(vMatchRate_Aggr)> '103.00%', 'Red',
IF(($(vMatchRate_Aggr)>='97.00%' and $(vMatchRate_Aggr)< '100.00%') OR ($(vMatchRate_Aggr)>'100.00%' and $(vMatchRate_Aggr)<= '103.00%'), 'Yellow')))

Re: Modify the logic

soniasweety — Thu, 22 Nov 2018 07:08:37 GMT

expression giving error 😞

Re: Modify the logic

PradeepReddy — Thu, 22 Nov 2018 07:15:56 GMT

working fine for me...
while creating the variable, don't use '=' symbol before expression.
If this is not resolving issue, let us know the exact error message.

Re: Modify the logic

soniasweety — Thu, 22 Nov 2018 07:24:14 GMT

i didnt use = symbol... stil its giving me ) expression error

Re: Modify the logic

soniasweety — Thu, 22 Nov 2018 07:25:19 GMT

can you atatch the app which u have done ?

Re: Modify the logic

soniasweety — Thu, 22 Nov 2018 07:25:52 GMT

can u attach the app which you have implemented.

Re: Modify the logic

Anil_Babu_Samineni — Thu, 22 Nov 2018 07:27:10 GMT

So, You want to get All three measures of count using the condition?

Re: Modify the logic

PradeepReddy — Thu, 22 Nov 2018 07:32:54 GMT

I can't upload the file.

Re: Modify the logic

soniasweety — Thu, 22 Nov 2018 07:33:07 GMT

Hi,

if you open the dashboard you can see pivot table .. right hand side you can seee two listbox and two text objects .

so what ever the green values coming into pivot table match rate column that count showing green text object and listbox same for all

Re: Modify the logic

soniasweety — Thu, 22 Nov 2018 07:42:53 GMT

can you paste the screenshot?

Re: Modify the logic

kaushiknsolanki — Thu, 22 Nov 2018 08:49:56 GMT

Hi,

Check last sheet in attached document.

Regards,

Kaushik Solanki

Re: Modify the logic

soniasweety — Thu, 22 Nov 2018 08:55:51 GMT

one final question if i want the three colours as a listbox. if user select one colur in the listbox that colur related values only should show in table how can i do that?

Re: Modify the logic

kaushiknsolanki — Thu, 22 Nov 2018 09:14:08 GMT

Hi,

Check the solution.

Regards,

Kaushik Solanki

Re: Modify the logic

soniasweety — Mon, 26 Nov 2018 20:16:30 GMT

Hi Sir

Kaushik_solanki

there is one issue coming .. i added one listbox with the colours but when is select that clour number rows is not showing proper in the table.

eaxmple: select 5x00 in red listbox. you can see 3 red colur values in pivot table. and select in listbox red then it is showing only 1 . "in copy of sheet tab"