Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
shamitshah
Partner - Creator
Partner - Creator

Conditional formatting

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

1 Solution

Accepted Solutions
tamilarasu
Champion
Champion

You can use below expression for new threshold.

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

View solution in original post

9 Replies
tamilarasu
Champion
Champion

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
MVP
MVP

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
Partner - Creator
Partner - Creator
Author

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
Partner - Creator
Partner - Creator
Author

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
Champion
Champion

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
Partner - Creator
Partner - Creator
Author

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
Champion
Champion

You can use below expression for new threshold.

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

shamitshah
Partner - Creator
Partner - Creator
Author

Thanks Tamil,

That works.