1 of 1 people found this helpful
I did not fully catch the gist, so my apologies if I'm way off base.
If you only want to show green flat when the previous three months
are blank or 0, then I think you will need to use a separate conditional other
than comparing zero to Difference +/- .
Not exactly sure how it would work, but one example would be,
if the Number of Records for a month will never be negative,
then you could write a separate conditional without the divide by 3:
ifnull( if datediff('month',[Date],today())<=3 and datediff('month',[Date],today())>=1 then [Number of Records] end, 0)
and then check if that sum is equal to zero.
So the overall IF/THEN would be
Also, should Green Down be for when Difference +/- is less than negative 1?
I guess your IF conditions malfunctioning and quite confusing
IF [Difference +/-] >= 1 THEN " Red Up" //If this condition met then your Green Down will never appear because you've put here>=1, It means If difference is greater or equals to 1 then It will be red but again in case of Green Down you've put <=1 which is again confusing and it will be skipped from that condition.
ELSEIF [Difference +/-] = 0 THEN "Green Flat" //Once it meet the condition then it will not be part of the next ELSEIF condition
ELSEIF [Difference +/-] > -1 AND [Difference +/-] < 1 THEN "Yellow No Change" // When you're putting condition >-1 and <1, zero is also falling in that range
ELSEIF [Difference +/-] <= 1 THEN "Green Down" //Only negative values <-1 will be in Green Down
You can try this not sure whether it will work or not
[Difference +/-] < 0 THEN "Yellow No Change"
ELSEIF [Difference +/-] = 0 THEN "Green Flat"
ELSEIF [Difference +/-] <= 1 THEN "Green Down"
ELSEIF [Difference +/-] > 1 THEN " Red Up"
You can create a sample mock up data which matches with structure of your original source in a packaged workbook and share with us.
Awesome, this really helped me out. Thanks for your assistance.