Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Conditional Format Date Range

I browsed the community and still am having trouble formatting values based on a given date range. For values that are less than the current month, I want to format them either red or green, while values greater than or equal to the current month colored grey. My current background color expression is as follows, any suggestions?

=
IF(
(
Sum({$<[Sales Data.Metric]={'Direct Revenue'}, [FiscalMonth]={">=$(=Max(FiscalMonth)<=$(=Max(FiscalMonth)"}> [Sales Data.Actual])
-
Sum({$<[Sales Data.Metric]={'Direct Revenue'}, [FiscalMonth]={">=$(=Max(FiscalMonth)<=$(=Max(FiscalMonth)"}>} [Sales Data.Plan])) < 0,
rgb(219,60,36),
IF(
(
Sum({$<[Sales Data.Metric]={'Direct Revenue'}, [FiscalMonth]={">=$(=Max(FiscalMonth)<=$(=Max(FiscalMonth)"}> [Sales Data.Actual])
-
Sum({$<[Sales Data.Metric]={'Direct Revenue'}, [FiscalMonth]={">=$(=Max(FiscalMonth)<=$(=Max(FiscalMonth)"}>} [Sales Data.Plan])) >= 0,
Green(),
rgb(128, 128, 128)))

1 Solution

Accepted Solutions

Re: Conditional Format Date Range

Hi Michael,

May be like this. 123456.JPG

I'm in the learning level. May be some expert will give the good idea.

21 Replies

Re: Conditional Format Date Range

Is your FiscalMonth a numeric field? or does it look like Jan, Feb..and so on?

Not applicable

Re: Conditional Format Date Range

Jan, Feb, ...., does it need to be numeric?

Re: Conditional Format Date Range

Max(FiscalMonth) converts the format of Month into Numeric field. Do you have a numeric equivalent of FiscalMonth in your data??? Something like FiscalMonthNo

Not applicable

Re: Conditional Format Date Range

Yes, DN = The numerical value of the date, ####

Highlighted

Re: Conditional Format Date Range

See if this helps:

=IF(
(
Sum({$<[Sales Data.Metric]={'Direct Revenue'}, DN={">=$(=MonthStart(Date(Max(DN)))) <=$(=MonthEnd(Date(Max(DN))))"}> [Sales Data.Actual])
-
Sum({$<[Sales Data.Metric]={'Direct Revenue'}, DN={">=$(=MonthStart(Date(Max(DN)))) <=$(=MonthEnd(Date(Max(DN))))"}>} [Sales Data.Plan])) < 0,
rgb(219,60,36),
IF(
(
Sum({$<[Sales Data.Metric]={'Direct Revenue'}, DN={">=$(=MonthStart(Date(Max(DN)))) <=$(=MonthEnd(Date(Max(DN))))"}>} [Sales Data.Actual])
-
Sum({$<[Sales Data.Metric]={'Direct Revenue'}, DN={">=$(=MonthStart(Date(Max(DN)))) <=$(=MonthEnd(Date(Max(DN))))"}>} [Sales Data.Plan])) >= 0,
Green(),
rgb(128, 128, 128)))

MVP
MVP

Re: Conditional Format Date Range

This might help:

Why don’t my dates work?

Not applicable

Re: Conditional Format Date Range

Still not working. I simply want to write out for the first f(x) if month is between MIN(DN) and Current(DN).

sasiparupudi1
Honored Contributor III

Re: Conditional Format Date Range

{">=$(=Max(FiscalMonth)<=$(=Max(FiscalMonth)"}

Do you only want the data for max(FiscalMonth)?.. It looks wrong to me

Not applicable

Re: Conditional Format Date Range

No, for the first and second IF() I want dates between JAN and Today, then for the third and fourth I want dates greater than or equal to Today.

Community Browser