topic Conditional Format Date Range in QlikView

Conditional Format Date Range

Thu, 16 Jul 2015 12:53:35 GMT

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

Re: Conditional Format Date Range

sunny_talwar — Thu, 16 Jul 2015 13:26:38 GMT

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

Re: Conditional Format Date Range

Thu, 16 Jul 2015 13:27:19 GMT

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

Re: Conditional Format Date Range

sunny_talwar — Thu, 16 Jul 2015 13:28:44 GMT

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

Re: Conditional Format Date Range

Thu, 16 Jul 2015 13:39:17 GMT

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

Re: Conditional Format Date Range

sunny_talwar — Thu, 16 Jul 2015 13:47:34 GMT

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

Re: Conditional Format Date Range

jonathandienst — Thu, 16 Jul 2015 13:49:25 GMT

This might help:

Why don’t my dates work?

Re: Conditional Format Date Range

Thu, 16 Jul 2015 14:03:27 GMT

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

Re: Conditional Format Date Range

sasiparupudi1 — Thu, 16 Jul 2015 14:04:34 GMT

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

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

Re: Conditional Format Date Range

Thu, 16 Jul 2015 14:07:32 GMT

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.

Re: Conditional Format Date Range

sunny_talwar — Thu, 16 Jul 2015 14:08:01 GMT

Sorry forgot to do one thing:

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

Re: Conditional Format Date Range

Thu, 16 Jul 2015 14:11:27 GMT

Still not working mate

Re: Conditional Format Date Range

Thu, 16 Jul 2015 15:46:28 GMT

Bump

Re: Conditional Format Date Range

settu_periasamy — Thu, 16 Jul 2015 16:58:00 GMT

Hi Michael,

is it possible to post the sample qvw file? it would give the better understanding about your data.

Re: Conditional Format Date Range

Thu, 16 Jul 2015 17:35:37 GMT

Please see attachments, again, my end result is the following. I want to show YTD, Full Accumulation, with months < Today() formatted GREEN() if ACTUALS beat PLAN, is ACTUAL are under PLAN, RED(). IF Months are >= Today(), format them RGB(128, 128, 128).

Example Chart: Conditional Coloring Based on Date Range

Re: Conditional Format Date Range

Thu, 16 Jul 2015 17:55:53 GMT

Any luck?

Re: Conditional Format Date Range

settu_periasamy — Thu, 16 Jul 2015 18:31:08 GMT

Hi,

if the YTD/ACTUALS is Full Accumulation, the value doesn't < YTD Plan value, right? (May be i understood wrongly)

See here..

Just i tried to remove the Full Accumulation for YTD ACTUALS . It looks like this

if it is full accumulation, then YTD ACTUALS/FORECAST > YTD PLAN, right?

maybe somebody will help...

Re: Conditional Format Date Range

Thu, 16 Jul 2015 18:41:55 GMT

Yes, that bottom graph looks correct, however, the third bar (the one for 3/31/2015 should be GREEN(), with the remainder before 7/31/2015 colored RED()

Re: Conditional Format Date Range

settu_periasamy — Fri, 17 Jul 2015 04:52:14 GMT

Hi Michael,

May be like this.

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

Re: Conditional Format Date Range

sasiparupudi1 — Fri, 17 Jul 2015 11:37:43 GMT

Hi Michhael,

I think you need an expression like the following. I am assuming that you have a fiscal date field in your data model..

You basically need to disregard all the other date releated selections like FiscalMonth,FiscalYear and just filter your data on your main date field(between Jan this year and year(Max(Your Date Field)) or Year(Today()).. Hope this helps

IF(

(Sum({$<[Sales Data.Metric]={'Direct Revenue'},FiscalMonth =, FiscalYear =, FiscalDate ={">=$(=YearStart(FiscalDate)<=$(=Year(Max(FiscalDate))"}> [Sales Data.Actual])

Sum({$<[Sales Data.Metric]={'Direct Revenue'}, FiscalMonth =, FiscalYear =, FiscalDate ={">=$(=YearStart(FiscalDate)<=$(=Year(Max(FiscalDate))">} [Sales Data.Plan])) < 0,

rgb(219,60,36),

IF(

(Sum({$<[Sales Data.Metric]={'Direct Revenue'}, FiscalMonth =, FiscalYear =, FiscalDate ={"<=$(=Max(FiscalDate)"}> [Sales Data.Actual])

Sum({$<[Sales Data.Metric]={'Direct Revenue'}, FiscalMonth =, FiscalYear =, FiscalDate ={"<=$(=Max(FiscalDate)"}>} [Sales Data.Plan])) >= 0,

Green(),

rgb(128, 128, 128)))