Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik Insider: SaaS capabilities for Data Integration and Analytics, May 25th! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
srl844
Contributor II
Contributor II

Color by Expression, 3 if statements

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

Labels (1)
1 Solution

Accepted Solutions
srl844
Contributor II
Contributor II
Author

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

View solution in original post

4 Replies
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
Contributor II
Contributor II
Author

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
Contributor II
Contributor II
Author

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