Color Expression Issue of Combo Chart in Qlik Saas

[Bharani26]

Hi All,

Need your help to solve this issue. 

I have an expression where I'm comparing Value1 vs Value2, if Value1 is greater than Value2 then I need Red() else Green(). When I tested, it is working in the table, but it is not working in the combo chart/ Bar chart.

In the table, the output is Red() which is correct, but in Chart the output is green() and also coming for both the bars which is wrong.

Value1 : Round(Sum({<Flag={'Canceled'},Deal_Stage={'Canceled'},Class_Profile={'BAU'},
ReportDate_Canceled={">=$(=Date(WeekStart(Today()-7),'DD/MM/YYYY'))<=$(=Date(WeekEnd(Today()-7),'DD/MM/YYYY'))"}>}UF_RECURRING_FEE)/
Sum({<Flag={'begin'},
ActiveMRFDate={">=$(=Date(WeekStart(Today()-7),'DD/MM/YYYY'))<=$(=Date(WeekEnd(Today()-7),'DD/MM/YYYY'))"}>}distinct [Con Active MRF Amount])
,0.001)

Value2:Round(Sum({<Flag={'Canceled'},Deal_Stage={'Canceled'},Class_Profile={'BAU'},
ReportDate_Canceled={">=$(=Date(WeekStart(Today()-14),'DD/MM/YYYY'))<=$(=Date(WeekEnd(Today()-14),'DD/MM/YYYY'))"}>}UF_RECURRING_FEE)/
Sum({<Flag={'begin'},
ActiveMRFDate={">=$(=Date(WeekStart(Today()-14),'DD/MM/YYYY'))<=$(=Date(WeekEnd(Today()-14),'DD/MM/YYYY'))"}>}distinct [Con Active MRF Amount])
,0.001)

Color Expression: 

if(

(Round(Sum({<Flag={'Canceled'},Deal_Stage={'Canceled'},Class_Profile={'BAU'},
ReportDate_Canceled={">=$(=Date(WeekStart(Today()-7),'DD/MM/YYYY'))<=$(=Date(WeekEnd(Today()-7),'DD/MM/YYYY'))"}>}UF_RECURRING_FEE)/
Sum({<Flag={'begin'},
ActiveMRFDate={">=$(=Date(WeekStart(Today()-7),'DD/MM/YYYY'))<=$(=Date(WeekEnd(Today()-7),'DD/MM/YYYY'))"}>}distinct [Con Active MRF Amount])
,0.001))

>

(Round(Sum({<Flag={'Canceled'},Deal_Stage={'Canceled'},Class_Profile={'BAU'},
ReportDate_Canceled={">=$(=Date(WeekStart(Today()-14),'DD/MM/YYYY'))<=$(=Date(WeekEnd(Today()-14),'DD/MM/YYYY'))"}>}UF_RECURRING_FEE)/
Sum({<Flag={'begin'},
ActiveMRFDate={">=$(=Date(WeekStart(Today()-14),'DD/MM/YYYY'))<=$(=Date(WeekEnd(Today()-14),'DD/MM/YYYY'))"}>}distinct [Con Active MRF Amount])
,0.001))

,Red(),Green()
)

Please check the attached image for the reference.

Please help me here. Thanks in advance.

[Marco_Imperiale]

Maybe you can try with =if(column(1)>column(2),Red(),Green())  ??

I know it's the same, but doing this you avoid any possible mistake in formula (parenthesys or other)...

[Marco_Imperiale]

...also I think you miss some parenthesys:

=if( (ALLTHECONDITIONA) > (ALLTHECONDITIONB), Red(), Green())
Because there is a flying *100 in front of the ">" sign... 

[Bharani26]

Hi @Marco_Imperiale, thanks for the response. Actually, I've also tried the way you mentioned, but the result is the same. Also tried using master measure and variables too, but in the chart the output is still green() not red().

[Marco_Imperiale]

You must use a dimension and then set an if() in color expression like this:

IF(WEEK=1 AND COLUMN(1)>COLUMN(2),RED(),
IF(WEEK=2 AND COLUMN(2)>COLUMN(1),RED(),GREEN()))

[Bharani26]

Hi again!

This is the dimension which I'm using, and the date field is from master calendar. 

if(WeekStart(Date) = WeekStart(Today()-14), 'Previous Week',
if(WeekStart(Date) = WeekStart(Today()-7), 'Current Week'))

[Marco_Imperiale]

Gather values in ETL for example in a field called "MyWeek" and then use it as dimesion of the chart.

TABNAME:
LOAD
    FIELD1,
    FIELD2,
    Date,
    if(WeekStart(Date) = WeekStart(Today()-14), 'Previous Week',
        if(WeekStart(Date) = WeekStart(Today()-7), 'Current Week')) as MyWeek

FROM XYZ.QVD

PS: maybe it's better the WeekName() function rather than WeekStart()...

    if(WeekName(Date) = WeekName(Today(),-1), 'Previous Week',
        if(WeekName(Date) = WeekName(Today()), 'Current Week')) as MyWeek