topic Re: Conditional formatting in QlikView

Conditional formatting

shamitshah — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Conditional formatting

tamilarasu — Fri, 04 Mar 2016 07:06:17 GMT

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

Re: Conditional formatting

tresB — Fri, 04 Mar 2016 07:12:12 GMT

In the script,

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

Background color condition:

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

Re: Conditional formatting

ramasaisaksoft — Fri, 04 Mar 2016 07:25:32 GMT

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.

Re: Conditional formatting

shamitshah — Fri, 04 Mar 2016 08:02:21 GMT

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

Re: Conditional formatting

shamitshah — Fri, 04 Mar 2016 08:25:23 GMT

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

Re: Conditional formatting

tamilarasu — Fri, 04 Mar 2016 09:06:20 GMT

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.

Re: Conditional formatting

shamitshah — Fri, 04 Mar 2016 09:24:59 GMT

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.

Re: Conditional formatting

tamilarasu — Sat, 05 Mar 2016 05:01:28 GMT

You can use below expression for new threshold.

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

Re: Conditional formatting

shamitshah — Sat, 05 Mar 2016 05:52:54 GMT

Thanks Tamil,

That works.