Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Re: Conditional Format Date Range

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

Not applicable

Re: Conditional Format Date Range

Still not working mate

Not applicable

Re: Conditional Format Date Range

Bump

Re: Conditional Format Date Range

Hi Michael,

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

Not applicable

Re: Conditional Format Date Range

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 graph.pngExample Chart: Conditional Coloring Based on Date Range

Not applicable

Re: Conditional Format Date Range

Any luck?

Highlighted

Re: Conditional Format Date Range

Hi,

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

See here..

1234.JPG

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

12345.JPG

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

maybe somebody will help...

Not applicable

Re: Conditional Format Date Range

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

Hi Michael,

May be like this. 123456.JPG

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

sasiparupudi1
Honored Contributor III

Re: Conditional Format Date Range

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

Community Browser