topic Re: Color by Expression, 3 if statements in App Development

Color by Expression, 3 if statements

srl844 — Fri, 15 Nov 2024 23:53:51 GMT

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))

Re: Color by Expression, 3 if statements

rubenmarin — Wed, 04 Aug 2021 06:26:52 GMT

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])

Re: Color by Expression, 3 if statements

srl844 — Wed, 04 Aug 2021 13:27:33 GMT

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!

Re: Color by Expression, 3 if statements

rubenmarin — Thu, 26 Aug 2021 09:02:03 GMT

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

Re: Color by Expression, 3 if statements

srl844 — Thu, 26 Aug 2021 12:35:50 GMT

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