Color Condition with Months

[#1]

Hi,

I don't know how far off I am but I want to use the Background Color condition of a Dimension to display Red if the Salesman has worked at our company for less than six months and green if the Salesman has worked at the company for longer. What I have is:

if( Month(Start_Date) > MonthStart(Today(- 6)), RGB(200,0,0), RGB(0,200,0))

Please could someone assist me to fix the condition so that it displays the correct colors.

Thanks,

Shane

[#2]

Use

if(Month(Start_Date) > Month(Addmonths(Today(),6)), RGB(200,0,0), RGB(0,200,0))

[maneshkhottcpl]

Hi,

Use This

if(addmonths('2010-06-01',6)<today(), RGB(200,0,0), RGB(0,200,0))

[Miguel_Angel_Baeyens]

Hello Shane,

Since dates are actually numeric values, you can add and substract them, so I'd use something like

If(Ceil(Num(Date(Today()) - Date(Start_Date)) / 30) < 6, RGB(200, 0, 0), RGB(0, 200, 0))

Hope that helps.

[#5]

Thanks Manesh and Bhaskar. Even I had the same requirement.

Regards,

Mandar

[maneshkhottcpl]

Hi,

Sorry use this , instead of my previous post

Use This

if(addmonths(Start_Date,6)<today(), RGB(200,0,0), RGB(0,200,0))

[#7]

Hi Everyone,

Thanks for all the replies. The result I'm getting is still wrong though (See image below) showing white when they are 2006, 2007, 2008 and 2009. Any suggestions for why that could be? I'm pulling Start_Date from an Excel file (Attached)

[maneshkhottcpl]

Hi,

Use This, if fail to work please send me ur application.

if(addmonths(Start_Date,6)<today(), RGB(0,200,0),, RGB(200,0,0))

[#9]

Maneshkhot,

I've attached a copy. Thanks, I look forward to your response

Shane

[#10]

Prueba creando en el Script:

Paso:

LOAD

Salesman,

addmonths(Start_Date,6) AS ff,

if(addmonths(Start_Date,6)<today(), RGB(0,200,0), RGB(200,0,0)) as V_Color

RESIDENT Invoices1;

Para la version 9 SR3, los colores en algunas lineas no se presentan, este es un problema de la tabla pivotante.