Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
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
settu_periasamy

Hi Michael,

May be like this. 123456.JPG

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

View solution in original post

21 Replies
sunny_talwar

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

Not applicable

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

sunny_talwar

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

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

sunny_talwar

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

jonathandienst

This might help:

Why don’t my dates work?

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

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

sasiparupudi1
Master III
Master III

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

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

Not applicable

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.