10 Replies Latest reply on Nov 12, 2013 9:11 AM by Joshua Milligan

# Conditional Colouring based on Last Non Empty value

Hi All,

I have been fiddling around with trying to get conditional formatting to work based on the last value of Actuals on the attached graph.

The logic is to compare the last bar on the graph to the forecast value (line) and then colour 'ALL' the bars based on the comparison result. i.e., if Actuals is less than Forecast for the last month, then all the bars should be Red and if the Actuals is greater than Forecast for the last month, then all the bars should be Green. My challenge, as you can see, is that I am not able to focus the conditionality on just the last month.

I have attached the packaged workbook for anyone who would fancy playing about.

Cheers

Siraj

• ###### 1. Re: Conditional Colouring based on Last Non Empty value

Siraj,

See if the attached workbook will meet your need.  Basically I used a calculation to walk through the table backwards (the table calc is sorted on date desc).  So then I could focus on the "first" value and disregard all others.

Regards,

Joshua

• ###### 2. Re: Conditional Colouring based on Last Non Empty value

Thank you for your time Joshua. Unfortunately, I am looking to colour 'ALL' the bars based on the state of the latest bar, so in the case above, all the bars would be RED (and no grey ones). Do you think that would be possible?

• ###### 3. Re: Conditional Colouring based on Last Non Empty value

Siraj,

Sure!  It's just a minor change to the calculation to carry forward the previous value across the rest of the bars.  I've attached an updated workbook.

Regards,

Joshua

• ###### 4. Re: Conditional Colouring based on Last Non Empty value

Hi Siraj:

Maybe I am missing something, but if you wanted all the bars to be the same color, couldn't you just change the default color of the bars (and not write any calcs?)  With all bars the same color, the last bar's significance is lost and the coloring has no meaning.  Perhaps I missed something, though.

Nevermind, I understand your logic now--if you do this, with all bars being the same color, it is extremely important that it is clear to the users that the color has meaning.  You can use the tooltip, legend, titles, etc. to relay that importance.

• ###### 5. Re: Conditional Colouring based on Last Non Empty value

Hi Joshua,

That works great for me - however, I am a bit puzzled about the workings of Previous_Value function. Could you help me understand the logic on your conditional colouring please?

Cheers

Siraj

• ###### 6. Re: Re: Conditional Colouring based on Last Non Empty value

Hi Joshua,

Having got to a solution (I wished), I have now hit another roadblock. The challenge that I overlooked is that the Actuals and Forecast come from two different data sources and I am using data blending to pick the last bar colour. As far as I can see, I think this now becomes probably difficult (unachievable?).

Can you think of a way which might be able to address the different data sources issue?

Regards

Siraj

• ###### 7. Re: Re: Conditional Colouring based on Last Non Empty value

Siraj,

In a calculated field, you can reference the aggregation of a field in another data source.  So, I've updated the calculation in the attached workbook to account for a secondary data source.

To explain the previous value:  just walk through the bar chart right to left.  The logic is:

• If the previous value is NULL or 0  (either we are looking at the first [from the right] month or the calculation did not give a result previously [meaning there were no actuals])
• Yes: Then check to see if there are any actuals.
• Yes: Compare to Forecast and give 1 for Green or -1 for Red
• No: Return NULL (implicit)
• No: (we have already determined either 1 or -1)  Return the Previous Value (the 1 or -1)

So in this screen shot,

1. December has a previous value of NULL and no Actuals, so NULL
2. November has a previous value of NULL and no Actuals, so NULL
3. October has a previous value of NULL has has Actuals.  Actuals > Forecast, so 1 (green)
4. September has a previous value of 1, so 1
5. August has a previous value of 1, so 1
6. etc..
7. etc..

• ###### 8. Re: Re: Conditional Colouring based on Last Non Empty value

Hi Joshua,

Thank you - that was very useful. I think that the Previous Value function could have a lot of uses. Thank you for introducing me to it! ;-)

One last question (I'll try and keep quiet after that) - Do you know if the same logic would work if I changed the measures to Table Cals (running totals)? So, for example.... something like this?

IF ZN(Previous_Value(SUM(0))) == 0

THEN

IF NOT ISNULL(SUM([Actuals]))

THEN

IF RUNNING_SUM(SUM([Actuals])) > RUNNING_SUM(SUM([Forecast]))

THEN 1 // Green

ELSE -1 // Red

END

END

ELSE Previous_Value(SUM(1))

END

Cheers

Siraj

• ###### 9. Re: Re: Re: Conditional Colouring based on Last Non Empty value

Hi Joshua,

I have been playing around with the package based on a need to now move all that measures to a running_sum.

Unfortunately, I have not been able to get Previous_Value to get me the result I am looking for. If anything, I thought that it is now looking from left to right, as opposed to right to left. Maybe I am doing it all wrong? I tried to use LOOKUP([Measure], LAST()) function to pick the last value as an alternate, but as you will see in the attached workbook, this does not work for cases where there is a forecast value and the actuals are a NULL.

Can you give me a hand and help me understand please?

Cheers

Siraj

PS - I have attached the workbook for your reference.

• ###### 10. Re: Re: Re: Conditional Colouring based on Last Non Empty value

Siraj,

I specifically set the sort of the table calculation to be by date descending -- that is why is is right to left.  (To see that, right click the field, select "Edit Table Calculation" and select "Advanced" from the drop down.)  If that doesn't help get you going, I'll be happy to take another look.

Regards,

Joshua