Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi there,
I have a table with a % Net recovery column in. I want to be able to colour this column three different colours based on the value.
0-69% Red
70-99% Orange
100%+ Green
I have done this successfully for <70% being Red otherwise everything else is Green as below and all works (the expression is further complicated by the complex data I have!):
if( num(
Pick(CompIndex
,
((Sum({<$(vCurrentFYSet)>} [Amount - $(=only(ChooseCurrency)) - ThirdPartyCosts])
+
SUM({<$(vCurrentFYSet)>} [Revenue Amount - $(=only(ChooseCurrency))])
)/
sum( {<$(vCurrentR12Set), ProjectTypeCode -= {'NC', 'IC'}>} [Amount - $(=ChooseCurrency) - TimeCosts])
)
,
((Sum({<$(vCurrentR12Set)>} [Amount - $(=only(ChooseCurrency)) - ThirdPartyCosts])
+
SUM({<$(vCurrentR12Set)>} [Revenue Amount - $(=only(ChooseCurrency))])
)/
sum( {<$(vCurrentR12Set), ProjectTypeCode -= {'NC', 'IC'}>} [Amount - $(=ChooseCurrency) - TimeCosts])
))
,'##0%') <0.70,RgbRed,RgbDarkGreen)
Would anyone be able to help me with adding the additional logic for the mid range? Is it possible in Qlik Sense?
Thank you,
Emma
I believe this is what you require:
if( num(
Pick(CompIndex
,
((Sum({<$(vCurrentFYSet)>} [Amount - $(=only(ChooseCurrency)) - ThirdPartyCosts])
+
SUM({<$(vCurrentFYSet)>} [Revenue Amount - $(=only(ChooseCurrency))])
)/
sum( {<$(vCurrentR12Set), ProjectTypeCode -= {'NC', 'IC'}>} [Amount - $(=ChooseCurrency) - TimeCosts])
)
,
((Sum({<$(vCurrentR12Set)>} [Amount - $(=only(ChooseCurrency)) - ThirdPartyCosts])
+
SUM({<$(vCurrentR12Set)>} [Revenue Amount - $(=only(ChooseCurrency))])
)/
sum( {<$(vCurrentR12Set), ProjectTypeCode -= {'NC', 'IC'}>} [Amount - $(=ChooseCurrency) - TimeCosts])
))
,'##0%') <0.70,
if( num(
Pick(CompIndex
,
((Sum({<$(vCurrentFYSet)>} [Amount - $(=only(ChooseCurrency)) - ThirdPartyCosts])
+
SUM({<$(vCurrentFYSet)>} [Revenue Amount - $(=only(ChooseCurrency))])
)/
sum( {<$(vCurrentR12Set), ProjectTypeCode -= {'NC', 'IC'}>} [Amount - $(=ChooseCurrency) - TimeCosts])
)
,
((Sum({<$(vCurrentR12Set)>} [Amount - $(=only(ChooseCurrency)) - ThirdPartyCosts])
+
SUM({<$(vCurrentR12Set)>} [Revenue Amount - $(=only(ChooseCurrency))])
)/
sum( {<$(vCurrentR12Set), ProjectTypeCode -= {'NC', 'IC'}>} [Amount - $(=ChooseCurrency) - TimeCosts])
))
,'##0%') >= 0.70 and
num(
Pick(CompIndex
,
((Sum({<$(vCurrentFYSet)>} [Amount - $(=only(ChooseCurrency)) - ThirdPartyCosts])
+
SUM({<$(vCurrentFYSet)>} [Revenue Amount - $(=only(ChooseCurrency))])
)/
sum( {<$(vCurrentR12Set), ProjectTypeCode -= {'NC', 'IC'}>} [Amount - $(=ChooseCurrency) - TimeCosts])
)
,
((Sum({<$(vCurrentR12Set)>} [Amount - $(=only(ChooseCurrency)) - ThirdPartyCosts])
+
SUM({<$(vCurrentR12Set)>} [Revenue Amount - $(=only(ChooseCurrency))])
)/
sum( {<$(vCurrentR12Set), ProjectTypeCode -= {'NC', 'IC'}>} [Amount - $(=ChooseCurrency) - TimeCosts])
))
,'##0%') < 1,
RgbRed,RgbDarkGreen
)
)
Hope this helps
Regards,
MB
PS: (WARNING) didn't tested it, may have problems with parenthesis
Thank you Miguel. I am not sure I see where the Orange (RgbOrange in my case) will come in?
I should have added to my original post that these are custom colours I am trying to apply.
I'm sorry, let me correct it
if( num(
Pick(CompIndex
,
((Sum({<$(vCurrentFYSet)>} [Amount - $(=only(ChooseCurrency)) - ThirdPartyCosts])
+
SUM({<$(vCurrentFYSet)>} [Revenue Amount - $(=only(ChooseCurrency))])
)/
sum( {<$(vCurrentR12Set), ProjectTypeCode -= {'NC', 'IC'}>} [Amount - $(=ChooseCurrency) - TimeCosts])
)
,
((Sum({<$(vCurrentR12Set)>} [Amount - $(=only(ChooseCurrency)) - ThirdPartyCosts])
+
SUM({<$(vCurrentR12Set)>} [Revenue Amount - $(=only(ChooseCurrency))])
)/
sum( {<$(vCurrentR12Set), ProjectTypeCode -= {'NC', 'IC'}>} [Amount - $(=ChooseCurrency) - TimeCosts])
))
,'##0%') <0.70,
RgbRed
if( num(
Pick(CompIndex
,
((Sum({<$(vCurrentFYSet)>} [Amount - $(=only(ChooseCurrency)) - ThirdPartyCosts])
+
SUM({<$(vCurrentFYSet)>} [Revenue Amount - $(=only(ChooseCurrency))])
)/
sum( {<$(vCurrentR12Set), ProjectTypeCode -= {'NC', 'IC'}>} [Amount - $(=ChooseCurrency) - TimeCosts])
)
,
((Sum({<$(vCurrentR12Set)>} [Amount - $(=only(ChooseCurrency)) - ThirdPartyCosts])
+
SUM({<$(vCurrentR12Set)>} [Revenue Amount - $(=only(ChooseCurrency))])
)/
sum( {<$(vCurrentR12Set), ProjectTypeCode -= {'NC', 'IC'}>} [Amount - $(=ChooseCurrency) - TimeCosts])
))
,'##0%') >= 0.70 and
num(
Pick(CompIndex
,
((Sum({<$(vCurrentFYSet)>} [Amount - $(=only(ChooseCurrency)) - ThirdPartyCosts])
+
SUM({<$(vCurrentFYSet)>} [Revenue Amount - $(=only(ChooseCurrency))])
)/
sum( {<$(vCurrentR12Set), ProjectTypeCode -= {'NC', 'IC'}>} [Amount - $(=ChooseCurrency) - TimeCosts])
)
,
((Sum({<$(vCurrentR12Set)>} [Amount - $(=only(ChooseCurrency)) - ThirdPartyCosts])
+
SUM({<$(vCurrentR12Set)>} [Revenue Amount - $(=only(ChooseCurrency))])
)/
sum( {<$(vCurrentR12Set), ProjectTypeCode -= {'NC', 'IC'}>} [Amount - $(=ChooseCurrency) - TimeCosts])
))
,'##0%') < 1,
RgbOrange,
RgbDarkGreen
)
)
This way is done I didn't saw the Orange RGB.
Hope this solves your issue.
Regards,
MB