Line color change and Accumlation

[brf10043]

Hi guys, I have a line chart that has two run lines.  The first is a target line and the second is an actual count line.  Both lines are set to Full Accumulation.  I would really like to be able to set the line properties of the actual count to be green if above the target line.

in other (non accumulating) charts I used something line this, but it doesn't work properly with accumulation.

if(Sum (IBM_Completed)
  >=
Sum (IBM_Planned)
,rgb(69,234,21),rgb(255,0,0) )

Also, is there a way to prevent the accumulation line form showing on future months?  I would like to apply the accumulation only to data that already exists.

Thanks

[sunny_talwar · Accepted solution]

Try this may be:

If(RangeSum(Above(Sum(IBM_Completed), 0, RowNo())) >= RangeSum(Above(Sum(IBM_Planned), 0, RowNo())), RGB(69, 234, 21), RGB(255,0,0))

[brf10043]

That works perfectly Sunny.  Got any suggestions for the second part?

Is there a way to prevent the accumulation line form showing on future months?  I would like to apply the accumulation only to data that already exists.

[sunny_talwar]

Hahahaha, I did not realize that there was a second part to the question. My eyes look for expressions . What can we use to define what is not in the future? Like a flag which is only available for today and the past? or Sales which are not available in the future?

[brf10043]

Take a look at this table.  It has data for Q1 and Q2.  I would like for the line chart to only have 2 data points (Q1 & Q2).  However, since I have Full accumulation turned on, I also get data points for Q2 & Q3.  I think the answer maybe to turn off accumulation in the properties and get the expression to perform the accumulation.  I don't know if that's the right approach, let alone how to do it.  My expression is very simple Sum (IBM_Completed)

Thoughts?

[sunny_talwar]

Why don't you use this expression with no accumulation

=RangeSum(Above(Sum({1}IBM_Completed), 0, RowNo())) * Avg(1)

[brf10043]

Unfortunately,  I get the same result as I do with the original expression with full accumulation.

[sunny_talwar]

How about this:

=RangeSum(Above(Sum({1<IBM_Completed *= {"=NullCount(IBM_Completed) = 0"}>}IBM_Completed), 0, RowNo())) * Avg(1)

[brf10043]

Sadly, It didn't work.  I still get the same result.

[sunny_talwar]

Would it be possible to share a sample?