Variable target in conditional pallette

[BigChris]

Hi Folks,

This might be an obvious question, but I can't seem to get this to work. I've got a column bar chart which currently has a fixed target to colour the bars either red or green. I'm trying to create a variable target dependant on the date. I've written the calculation without any trouble:

Code Select Expand

if(total([Enterprise Layer].[Sales Order Line History].[Nett Invoice Value] for [Enterprise Layer].[Sales Order Line History].[Document Date]) <

CASE
when [Enterprise Layer].[Sales Order Line History].[Document Date] between 2014-09-28 and 2014-10-25 then 42578
when [Enterprise Layer].[Sales Order Line History].[Document Date] between 2014-10-26 and 2014-11-29 then 40554
when [Enterprise Layer].[Sales Order Line History].[Document Date] between 2014-11-30 and 2014-12-27 then 39256
when [Enterprise Layer].[Sales Order Line History].[Document Date] between 2014-12-28 and 2015-01-31 then 40353
when [Enterprise Layer].[Sales Order Line History].[Document Date] between 2015-02-01 and 2015-10-28 then 41054
when [Enterprise Layer].[Sales Order Line History].[Document Date] between 2015-03-01 and 2015-03-28 then 41449
else 39000
END
)
then ('Red') else ('Green')

That produces the right results, with the text field set to either Red or Green as expected. Now for the bit that i'm struggling with - how can I use that in the conditional pallette for the chart? I've attached the current chart that I'm trying to improve...any help would be gratefully received.

C

[Lynn]

All that hard coding is a tad bit frightening, but I digress.

Instead of having that expression in a data item can you use an advanced conditional style in your conditional palette? You would have one expression written as a boolean that would evaluate true for the green condition and you'd set the color to green for that one. Then you'd add another one using the same expression but evaluating as true for the red condition with, of course, the appropriate styling there.

The "if" would go away and you'd use < for the red situation and set the color for this condition to red:

Code Select Expand


[YourTotalItem] <
  CASE
     blah, blah, blah
  END


And then perhaps >= for the green situation and set the color to green for this one.

Code Select Expand


[YourTotalItem] >=
  CASE
     blah, blah, blah
  END


[BigChris]

I can see where you're going with that - I'll give it a try and post back.

Incidentally, I agree with you about the hard coding...I'm hoping to get a table set up in the database with those date ranges and targets in so that they can be updated centrally, but this is needed yesterday (shock!), hence the quick and dirty method.

[BigChris]

Hi - I've managed to find a way to do it...possibly not the most elegant solution, but it works. I set the default colour for the graph to be red, which just left me with the problem of identifying the green days. I tried Lynn's suggestion above, but I couldn't get that to play nicely. The method I used was to set up an advanced condition in the conditional pallette with the following condition:

Code Select Expand

([Query1].[Document Date] between '2014-09-28' and '2014-10-25' and [Query1].[Nett Invoice Value] > 42578) or
([Query1].[Document Date] between '2014-10-26' and '2014-11-29' and [Query1].[Nett Invoice Value] > 40554) or
([Query1].[Document Date] between '2014-11-30' and '2014-12-27' and [Query1].[Nett Invoice Value] > 39256) or
([Query1].[Document Date] between '2014-12-28' and '2015-01-31' and [Query1].[Nett Invoice Value] > 40353) or
([Query1].[Document Date] between '2015-02-01' and '2015-02-28' and [Query1].[Nett Invoice Value] > 41054) or
([Query1].[Document Date] between '2015-03-01' and '2015-03-28' and [Query1].[Nett Invoice Value] > 41449)
or [Query1].[Nett Invoice Value] > 41008

for which I set the colour to be green.

I recognise that it's all hard-coded and manual, but for now at least it works and gives me the output I need.

Just thought I'd share in case anyone comes across a similar issue.

C

[Lynn]

Sometimes we gotta do what we gotta do. Glad you found a solution!