Solved: Re: Modify the logic - Qlik Community

soniasweety · ‎2018-11-21

HI

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

kaushiknsolanki · ‎2018-11-22

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!

View solution in original post

PradeepReddy · ‎2018-11-22

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')))

View solution in original post

sunny_talwar · ‎2018-11-26

Can you try running this

test:
LOAD Num(Alt(..., 0), '#,##0.00%') as [Match Rate],
     RowNo() as RowNo
FROM <> (qvd);

I think the problem is that right now your calculation is leading to a null rather than 0...

I am hoping that after you add the Alt() function... you will see 0 instead of null which should resolve your issue.

View solution in original post

kaushiknsolanki · ‎2018-11-22

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!

soniasweety · ‎2018-11-22

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.

Anil_Babu_Samineni · ‎2018-11-22

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] ??

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

soniasweety · ‎2018-11-22

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

soniasweety · ‎2018-11-22

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

PradeepReddy · ‎2018-11-22

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')))

soniasweety · ‎2018-11-22

expression giving error 😞

PradeepReddy · ‎2018-11-22

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.

soniasweety · ‎2018-11-22

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