May 15, 2012 at 8:34 am
Hi everyone
I hope somebody can help. I have the following SWITCH statement in one of my reports:
=Switch(sum(Fields!YTDBudget.Value)/sum(Fields!YTDActual.Value) -1 <-2.5, "Green", sum(Fields!YTDBudget.Value)/sum(Fields!YTDActual.Value) -1 <=-5.0, "Yellow", sum(Fields!YTDBudget.Value)/sum(Fields!YTDActual.Value) -1 >-5, "Red")
Essentially it calculates the Budget/Actual difference and then RAG rates the result. However, it's not working as it should. I'd like the RAG to be Green when the result is less than -2.5%, Yellow when it's greater then -2.5% but less than -5.0% and finally Red when greater than -5.0%.
The current function does not appear to be working and the result is always red (I know that this is not the case). I'm assuming it has something to do with the negative numbers but not sure what exactly...
Any help would be really appreciated...
BO
May 15, 2012 at 8:36 am
You've defined a mathematical paradox there. -2.5 is greater than -5, so your rules are overriding each other. When you say "greater than -5", do you really mean "below -5", like -6, -7, -100, or do you mean "greater than -5", like -4, -3, -2, +1, +2, +100?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 15, 2012 at 8:43 am
I actually want it to be below -5 (ie -6, -7 etc like you say...)
May 15, 2012 at 8:47 am
ByronOne (5/15/2012)
I actually want it to be below -5 (ie -6, -7 etc like you say...)
Change the function to <-5 instead of >-5, see if that does what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 15, 2012 at 8:56 am
Sorry for being stupid but do you mean like the following:
=Switch(sum(Fields!YTDBudget.Value)/sum(Fields!YTDActual.Value) -1 <-2.5, "Green", sum(Fields!YTDBudget.Value)/sum(Fields!YTDActual.Value) -1 <=-5.0, "Yellow", sum(Fields!YTDBudget.Value)/sum(Fields!YTDActual.Value) -1 <-5, "Red")
I've tried this but it seems to confuse it and this time i get no color returned....?
May 15, 2012 at 9:02 am
ByronOne (5/15/2012)
Sorry for being stupid but do you mean like the following:=Switch(sum(Fields!YTDBudget.Value)/sum(Fields!YTDActual.Value) -1 <-2.5, "Green", sum(Fields!YTDBudget.Value)/sum(Fields!YTDActual.Value) -1 <=-5.0, "Yellow", sum(Fields!YTDBudget.Value)/sum(Fields!YTDActual.Value) -1 <-5, "Red")
I've tried this but it seems to confuse it and this time i get no color returned....?
I reformated the code a bit to get a better look. Looks to me like you have two criteria looking for <= -5 or < -5:
=Switch(sum(Fields!YTDBudget.Value)/sum(Fields!YTDActual.Value) -1 <-2.5, "Green",
sum(Fields!YTDBudget.Value)/sum(Fields!YTDActual.Value) -1 <=-5.0, "Yellow",
sum(Fields!YTDBudget.Value)/sum(Fields!YTDActual.Value) -1 <-5, "Red")
May 15, 2012 at 9:23 am
Lynn Pettis (5/15/2012)
ByronOne (5/15/2012)
Sorry for being stupid but do you mean like the following:=Switch(sum(Fields!YTDBudget.Value)/sum(Fields!YTDActual.Value) -1 <-2.5, "Green", sum(Fields!YTDBudget.Value)/sum(Fields!YTDActual.Value) -1 <=-5.0, "Yellow", sum(Fields!YTDBudget.Value)/sum(Fields!YTDActual.Value) -1 <-5, "Red")
I've tried this but it seems to confuse it and this time i get no color returned....?
I reformated the code a bit to get a better look. Looks to me like you have two criteria looking for <= -5 or < -5:
=Switch(sum(Fields!YTDBudget.Value)/sum(Fields!YTDActual.Value) -1 <-2.5, "Green",
sum(Fields!YTDBudget.Value)/sum(Fields!YTDActual.Value) -1 <=-5.0, "Yellow",
sum(Fields!YTDBudget.Value)/sum(Fields!YTDActual.Value) -1 <-5, "Red")
And none at all for any number above -2.5. Like -1 or 0 or any positive number at all. Do these allow ELSE functions in the rules, or something comparable?
What you need is something like:
=Switch(sum(Fields!YTDBudget.Value)/sum(Fields!YTDActual.Value) -1 >-2.5, "Green",
sum(Fields!YTDBudget.Value)/sum(Fields!YTDActual.Value) -1 <-5, "Red"
ELSE, "Yellow")
I'm not sure how you'd write the final rule, to make it yellow if it's not green or red per the other rules.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 15, 2012 at 10:47 am
Cheers guys
I'll have aplay around to see if I can work it out...
May 15, 2012 at 11:09 am
I found a suggestion that says that if you want a default, use 1=1 as the test, and put it last. Haven't tried that, but it would go where I have "ELSE" if you want to test it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 15, 2012 at 12:07 pm
Yes, 1 = 1 will work as a functional else in a SWITCH statement.
I see a lot of back and forth regarding less than and greater than with negative numbers. Perhaps this is where the problem lies.
In cases like this I will usually put my base formula sum(Fields!YTDBudget.Value)/sum(Fields!YTDActual.Value) -1 in a textbox all by itself, just so I can verify that I am getting the values I expect in my switch. This frequently leads to useful insights.
I also wonder what you want to happen when the value is greater than -2.5, for example -1. You do not seem to account for all possibilities.
May 17, 2012 at 2:58 am
Thanks everyone.
I'm still having problems but I'm wondering whether I've not been clear in my original query.
I'm trying to RAG in the following way:
Variance greater than 5% negative = RED
Variance greater than 2.5% negative = YELLOW
Variance less than 2.5% negative = GREEN
I'm currently using the foloowing switch function:
=Switch(sum(Fields!YTDBudget.Value)/sum(Fields!YTDActual.Value) -1 > -2.5, "Green",sum(Fields!YTDBudget.Value)/sum(Fields!YTDActual.Value) -1 < -5.0 , "Red", 1=1, "Yellow")
This is returning GRENN for each of my indicators (they are -3.7%, -0.4% and -10.1% so should be YELLOW, GREEN and RED respectively...)
May 17, 2012 at 9:23 am
ByronOne (5/17/2012)
Thanks everyone.I'm currently using the foloowing switch function:
=Switch(sum(Fields!YTDBudget.Value)/sum(Fields!YTDActual.Value) -1 > -2.5, "Green",sum(Fields!YTDBudget.Value)/sum(Fields!YTDActual.Value) -1 < -5.0 , "Red", 1=1, "Yellow")
This is returning GRENN for each of my indicators (they are -3.7%, -0.4% and -10.1% so should be YELLOW, GREEN and RED respectively...)
Your sample values should all return green. You are evaluating percent values against non percent values. Try using -.025 and -.05 for the values in your SWITCH statement.
May 17, 2012 at 9:53 am
Daniel - you're amazing!!
Now works perfectly....
Cheers
BO
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply