Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
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)))

21 Replies
Not applicable
Author

Why did you include, Round(), and further, when I applied the formula to my chart, it came out as follows as all red, with the green bar as grey.

Not applicable
Author

Never mind, I fixed it as follows:

=
IF(
Sum({$<[Metric]={'Direct Revenue'}, DATE={">=$(=today(DATE)) <=$(=Max(DATE))"}>}[Act/Fcst]), rgb(128, 128, 128),

IF(
(
Sum({$<[Metric]={'Direct Revenue'}, DATE={">=$(=Min(DATE)) <$(=today(DATE))"}>}[Act/Fcst]) -
Sum({1<[Metric]={'Direct Revenue'}, DATE={">=$(=Min(DATE)) <$(=today(DATE))"}>}[Plan])) < 0,rgb(219,60,36),

IF(
(
Sum({$<[Metric]={'Direct Revenue'}, DATE={">=$(=Min(DATE)) <$(=today(DATE))"}>}[Act/Fcst]) -
Sum({1<[Metric]={'Direct Revenue'}, DATE={">=$(=Min(DATE)) <$(=today(DATE))"}>}[Plan])) >= 0,Green())))