Conditional formatting

[shamitshah]

Hi,

Please see attached file and need assistance to do the following:

1) Amend the script so that the date is combined in 1 column in the format DD-MM-YYYY

2) Conditional format the date cells to highlight e.g. Sales < 500 prior to today()

Thanks

Shamit

[tamilarasu]

Hi,

Check the attachment.

Date expression:

Makedate(Year, Num(Month(date#(Month,'MMM'))), Date)

You can use this while load the data like Makedate(Year, num(month(date#(Month,'MMM'))), Date) as CompleteDate

Conditional Format Expression:

If(Makedate(Year, Num(Month(date#(Month,'MMM'))), Date)< Today() and Sum(Sales)<500, LightRed(),LightGreen())

[tresesco]

In the script,

      Date(Date#(Year&Month&Date,'YYYYMMMD') as NewDate

Background color condition:

=If(NewDate<Today() and Sum(Sales)<500, LightRed())

[ramasaisaksoft]

Hi Shamit,

for the specified format the format DD-MM-YYYY u can use like

Date &''& Month&''&  Year as fullday

select the color as red

and check the attachment screen shot.

[shamitshah]

Hi Tamil,

Thanks for your response.

Currently all the cells in the Sales column are highlighted either red or green.

I will try to tweak your solution so that Sales with dates > today() do not get highlighted.

Shamit

[shamitshah]

Hi Rama

Please see my earlier response to Tamil.

In specific, I only want to highlight the cells where Sales < 500, but only in relation to sales prior to today().

The solution proposed by Tamil on the date format works. I have added it as an added dimension.

Thanks

[tamilarasu]

Hi Shamit,

I have added lightgreen() to highlight the cells where the sales > 500. If you want to highlight for sales < 500, then simply use,

If(CompleteDate < Today(), If(Sum(Sales) < 500, LightRed(),Lightgreen()))

You can use some light colors by using RGB() function.

Edit: I misunderstood your last response. Updated the expression.

[shamitshah]

Please see attached.

I have now included the date solution as a dimension as proposed by Tamil,

I incorporated an expression in the background color option under this dimension.

Any idea how I can amend it so that Sales < 200 are highlighted red and Sales > 200 are highlighted green at same time taking into account the date? I have the changed the threshold from 500 to 200.

[tamilarasu · Accepted solution]

You can use below expression for new threshold.

If(DateField< Today(), If(Sum(Sales)<200, LightRed(),LightGreen()))

[shamitshah]

Thanks Tamil,

That works.