
1. Re: Conditional Colouring based on Last Non Empty value
Joshua Milligan Nov 8, 2013 1:56 PM (in response to sirajmandayippurath)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
sirajmandayippurath Nov 10, 2013 2:40 PM (in response to Joshua Milligan)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
Joshua Milligan Nov 10, 2013 7:43 PM (in response to sirajmandayippurath)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
Matt Lutton Nov 11, 2013 5:40 AM (in response to sirajmandayippurath)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 nowif 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
sirajmandayippurath Nov 11, 2013 5:42 AM (in response to Joshua Milligan)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?
Many thanks in advance!
Cheers
Siraj

6. Re: Re: Conditional Colouring based on Last Non Empty value
sirajmandayippurath Nov 11, 2013 7:16 AM (in response to sirajmandayippurath)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
Joshua Milligan Nov 11, 2013 9:15 AM (in response to sirajmandayippurath)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,
 December has a previous value of NULL and no Actuals, so NULL
 November has a previous value of NULL and no Actuals, so NULL
 October has a previous value of NULL has has Actuals. Actuals > Forecast, so 1 (green)
 September has a previous value of 1, so 1
 August has a previous value of 1, so 1
 etc..
 etc..

8. Re: Re: Conditional Colouring based on Last Non Empty value
sirajmandayippurath Nov 11, 2013 11:30 PM (in response to Joshua Milligan)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
Thanks in advance!
Cheers
Siraj

9. Re: Re: Re: Conditional Colouring based on Last Non Empty value
sirajmandayippurath Nov 12, 2013 8:29 AM (in response to Joshua Milligan)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?
Many thanks in advance!
Cheers
Siraj
PS  I have attached the workbook for your reference.

10. Re: Re: Re: Conditional Colouring based on Last Non Empty value
Joshua Milligan Nov 12, 2013 9:11 AM (in response to sirajmandayippurath)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