Color Conditions in a Variable not working properly

[cbyrne]

Hello,

Can anyone tell me where i am going wrong with the below condition, i have a data item _months_between (CURRENT_DATE, [Service Date]) the service date is when the asset is due for a service, my report color needs to show when 6months before the service date show orange when 12 months show white and when 13 months show red.



CASE WHEN ([QryMasterDate].[Months Overdue]>=1)THEN'ORANGE' WHEN ([QryMasterDate].[Months Overdue]>=55)THEN 'RED' WHEN ([QryMasterDate].[Months Overdue]<=98) THEN 'WHITE' END

Many Thanks for any help in advance


Best Regards


Chris

[Lynn]

I personally prefer advanced conditional styling over style variables in case you want to attempt an alternate approach.

What is the behavior you are seeing? Are things you expect to see in red turning up as orange?

Case logic stops evaluating once a condition is matched, so your first "when" sets the string variable value to orange for >=1 so it would never reach the >=55 condition. Changing the order of the clauses, or using mutually exclusive "between" ranges could fix this.

I generally include an "else" to explicitly cover, well, everything else. This is also handy for debugging because it will show when things fall completely out of the logic when I'm not expecting anything to do that. Seems to me you can have a lot of values outside the ranges you define.

Just sticking with what you have, see if the reordering below changes the behavior.

Code Select Expand


CASE
WHEN ([QryMasterDate].[Months Overdue]<=98) THEN 'WHITE'
WHEN ([QryMasterDate].[Months Overdue]>=55)THEN 'RED'
WHEN ([QryMasterDate].[Months Overdue]>=1)THEN 'ORANGE'
END


[cbyrne]

Thanks lynn,

Yes this seem to work better plus it is more readable and understandable.

What i am unsure of is the outputt data, it's a pitty i cannot show you the output report. Please see the below columns.
Basically i the days overdue date is current_date, ServiceDate what i want to show is orange at 6 months and red at 12 months or when the asset reaches its service date.  Also below is my current workings out to try and resolve this plus i have written in your layout.


Service Due Date   Days Overdue
25 Apr 2014   -228
25 Apr 2014   -228
22 Apr 2014   -225
25 Mar 2014   -197
29 Apr 2014   -232
22 Apr 2014   -225
25 Apr 2014   -228
23 Apr 2014   -226
25 Mar 2014   -197
29 Apr 2014   -232
3 Apr 2014   -206
22 Apr 2014   -225
29 Apr 2014   -232
1 Apr 2014   -204
23 Apr 2014   -226
24 Apr 2014   -227
30 Apr 2014   -233
1 Apr 2014   -204
30 Apr 2014   -233

CASE
WHEN([QryMasterData1].[Days Overdue]<=182)THEN 'ORANGE'
WHEN ([QryMasterData1].[Days Overdue]>=183)THEN 'RED'
WHEN ([QryMasterData1].[Days Overdue]>=1)THEN 'WHITE'
END


Many Thanks


Chris

[Lynn]

Hmm....you are getting negative numbers but using positive numbers in your condition. Everything is less than zero so it is all going to match the first when clause and fall into orange.

There is a _months_between function that might be a better fit with your requirement since the number of days per month varies.

Does the below seem to fit with what you need? Placing the date arguments in that order yields a positive number, so just set the conditions as needed from there.

Code Select Expand


case _months_between(current_date, [Service Due Date]
when 6 then 'ORANGE'
when 12 then 'RED'
else 'WHITE'
end


[cbyrne]

Hi lynn,

I keep getting an error with your code, the only difference i made to yours was the bracket at the end of the service due date.



Kind Regards

Chris

[RKMI]

Think she was trying to use _add_months function.

Code Select Expand

case _add_months between(current_date, [Service Due Date])
when 6 then 'ORANGE'
when 12 then 'RED'
else 'WHITE'
end


[Lynn]

I mistakenly omitted the closing parenthesis. The _months_between function returns the number of months between two dates.

What is the error you are getting? Alternate approach to case syntax is below.

Code Select Expand


case
when _months_between(current_date, [Service Due Date] ) = 6 then 'ORANGE'
when _months_between(current_date, [Service Due Date] ) = 12 then 'RED'
else 'WHITE'
end


[cbyrne]

I get a parsing error failed to validate the expression at line 40 in the expression Lynn.

case
when _months_between (CURRENT_DATE, [Service Date]) = 6 then 'ORANGE'
when _months_between (CURRENT_DATE, [Service Date]) = 12 then 'RED'
else 'WHITE'
end

Thanks

Chris

[cbyrne]

Hi Lynn,  I have added the below into a data item then added it to my list report to check the values and this works fine however it wont let me put it in as a variable condition for some reason.

case
when _months_between (CURRENT_DATE, [Service Date]) = 6 then 'ORANGE'
when _months_between (CURRENT_DATE, [Service Date]) = 12 then 'RED'
else 'WHITE'
end

Thanks

Chris

[cbyrne]

Hi Lynn, i have amended the data item for CURRENT_DATE, [Service Date] to [Service Date], CURRENT_DATE so as to remove the negative, the below variable works when i validate, however when i add this to my dataItem column in the page in the style variable i then get an error for this variable.

case
when _days_between ([QryMasterData1].[Days Overdue]) >= 182 then 'ORANGE'
when _days_between ([QryMasterData1].[Days Overdue]) >= 0 then 'RED'
when _days_between ([QryMasterData1].[Days Overdue]) = 2 then 'WHITE'
end

Thanks for your help so far.

Thanks

Chris

[cbyrne]

This is the error i get below.



RSV-VAL-0002 Invalid expression case
when _days_between ([QryMasterData1].[Days Overdue]) >= 182 then 'ORANGE'
when _days_between ([QryMasterData1].[Days Overdue]) >= 0 then 'RED'
when _days_between ([QryMasterData1].[Days Overdue]) = 2 then 'WHITE'
end. CRX-API-0013 The number of arguments in the function "_days_between" is not valid at or near the position '25'.


Thanks


Chris

[Lynn]

Hi Chris,
Your error is saying that you aren't passing the right number of arguments to the _days_between function. It calculates the number of days between TWO dates and you've passed only one date.

I don't use style variables because I think conditional styles are infinitely easier to create and maintain.

Here are two links that explain how to highlight using a style variable. Combined these two topics are 20 steps.

The third link explains how to create an advanced conditional style which requires 6 steps.

http://publib.boulder.ibm.com/infocenter/c8bi/v8r4m0/index.jsp?topic=/com.ibm.swg.im.cognos.ug_cr_rptstd.8.4.0.doc/ug_cr_rptstd_id24211cr_rptstd_modrep_highlight_data_var.html

http://publib.boulder.ibm.com/infocenter/c8bi/v8r4m0/index.jsp?topic=/com.ibm.swg.im.cognos.ug_cr_rptstd.8.4.0.doc/ug_cr_rptstd_id24211cr_rptstd_modrep_highlight_data_var.html

http://pic.dhe.ibm.com/infocenter/cbi/v10r2m0/index.jsp?topic=%2Fcom.ibm.swg.ba.cognos.ug_cr_rptstd.10.2.0.doc%2Ft_cr_rptstd_modrep_create_adv_cond_style.html

[cbyrne]

Thanks very much Lynn for pointing me in the right direction, it's took me hours of testing and getting it wrong until i tried a conditional style and changed my dataItem as below, bit long winded but it seems to work, i am still playing around with it, oh and i was at first trying to enter numeric conditional styles but kept getting errors because of the data types so i used string values.

case
when _months_between ([Service Date], CURRENT_DATE) =0 then 'RED'
when _months_between ([Service Date], CURRENT_DATE) =1 then 'WHITE'
when _months_between ([Service Date], CURRENT_DATE) =2 then'WHITE'
when _months_between ([Service Date], CURRENT_DATE)=3 then'WHITE'
when _months_between ([Service Date], CURRENT_DATE)=4 then'WHITE'
when _months_between ([Service Date], CURRENT_DATE)=5 then'WHITE'
when _months_between ([Service Date], CURRENT_DATE)=6 then'ORANGE'
when _months_between ([Service Date], CURRENT_DATE)=7 then'WHITE'
when _months_between ([Service Date], CURRENT_DATE)=8 then'WHITE'
when _months_between ([Service Date], CURRENT_DATE)=9 then'WHITE'
when _months_between ([Service Date], CURRENT_DATE)=10 then'WHITE'
when _months_between ([Service Date], CURRENT_DATE)=11 then'WHITE'
when _months_between ([Service Date], CURRENT_DATE)=12 then'WHITE'
else 'WHITE'

Thanks Again

Chris

[Lynn]

Glad to hear you found a way through  :D

[cbyrne]

Hi Lynn, i need to do this now for days instead of months however i am not sure of the day condition.

When it gets to 182 days no fill, i.e. can stay white.

182 days to 0 should be orange.

when 0 days then should be red.

So i think from 1 to 182 should stay white but how can i do the calculation from 1 to 182.

case
when _days_between ([Service Date], CURRENT_DATE) = 182 then 'WHITE'
when _days_between ([Service Date], CURRENT_DATE)  >183 then 'ORANGE'
when _days_between ([Service Date], CURRENT_DATE) = 0 then 'RED'
end


Many Thanks


Chris

[Lynn]

Check to see what values get returned by your _days_between function. The order of the operands is important so ( [Service Date], CURRENT_DATE ) is different than ( CURRENT_DATE, [Service Date] )