Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Partner
Partner

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

You can use below expression for new threshold.

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

View solution in original post

9 Replies

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())

MVP
MVP

In the script,

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

Background color condition:

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

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.

Partner
Partner

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

Partner
Partner

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

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.


Partner
Partner

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.

You can use below expression for new threshold.

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

View solution in original post

Partner
Partner

Thanks Tamil,

That works.