Color by Expression, 3 if statements

[srl844]

I am trying to update an app that currently using a color by expression with an if statement (so two colors are present). I am trying to add a third color/filter.  I have included the code below. The additional filter I am trying to add is to provide a different color for anyone who has a 0/null value for <epf={1},[Year]={$(=vCurrentYear-1)},[Month]={"<$(vCurrentMonth)"}>

if(avg({<Coached={1}>}
    aggr((
        avg({<epf={1},[Year]={$(=vCurrentYear)},[Month]={"<$(vCurrentMonth)"}>}if(aggr(sum(Cost),PersonTime)<>0,aggr(Sum(distinct {<epf={1}>}CashReceived),PersonTime)))
                -
        avg({<epf={1},[Year]={$(=vCurrentYear-1)},[Month]={"<$(vCurrentMonth)"}>}if(aggr(sum(Cost),PersonTime)<>0,aggr(Sum(distinct {<epf={1}>}CashReceived),PersonTime))))
        /
        avg({<epf={1},[Year]={$(=vCurrentYear-1)},[Month]={"<$(vCurrentMonth)"}>}if(aggr(sum(Cost),PersonTime)<>0,aggr(Sum(distinct {<epf={1}>}CashReceived),PersonTime)))
    ,PersonKey))>0

,rgb(188,214,235), rgb(199,49,26))

[rubenmarin]

Hi, maybe with:

if(Alt(avg({<Coached={1}>}
  avg({<epf={1},[Year]={$(=vCurrentYear-1)},[Month]={" 
  <$(vCurrentMonth)"}>}if(aggr(sum(Cost),PersonTime)<>0,aggr(Sum(distinct {<epf={1}>}CashReceived),PersonTime)))
,PersonKey)),0)=0, RGB(color), [YourCurrentIf])

[srl844]

Hey Ruben- thanks for this suggestion. Giving this a try, it just turns all of my bars to the new color. I have tried adjusting the new if statement, but it still leaves everything gray. Any additional thoughts? Thanks for help!

[rubenmarin]

Hi, I was on holidays, you still need help with this or it's already solved?

[srl844 · Accepted solution]

Hey Ruben- Thanks for checking in. This was very close, but did take some tweaking. We were able to solve it though! We ended up just needing to take the denominator and change it to =0

avg({<epf={1},[Year]={$(=vCurrentYear-1)},[Month]={"<$(vCurrentMonth)"}>}if(aggr(sum(Cost),PersonTime)<>0,aggr(Sum(distinct {<epf={1}>}CashReceived),PersonTime)))
    ,PersonKey))=0