Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
jmonroe918
Creator II
Creator II

Conditional Count (with Color)

I am creating a stacked bar chart that displays all open CAPA records by Division (the dimension). I also want each bar to reflect subsets of age ranges for the open records (<30, Between 30 & 90 and >90 days) showing as green, yellow and red,respectively.

I have a set analysis that filters out all of the "non-closed" records (Count({$<Status=Status-{'Closed'}>} [CAPA No])) as an expression, however I have not been able to figure out how to count the open records for each site based on each "Age (Days)" range so I can see for each division a bar stacked with green, yellow and red bands, as applicable.

Jeff

1 Solution

Accepted Solutions
rwunderlich

You can pre-calc the bucket in the script or create it as a Calculated Dim in your chart like this:

=if([Age (Days)] > 90, '>90'

,if([Age (Days)] > 30, '30-90'

,'<30'

))

I assigned the colors in the pallate order. If you want to get more specific you can use the background color attribute.

Also, I think your set modifier shoule be:

Status-={'Closed'}

See attached.

-Rob

http://masterssummit.com

View solution in original post

6 Replies
rwunderlich

You can pre-calc the bucket in the script or create it as a Calculated Dim in your chart like this:

=if([Age (Days)] > 90, '>90'

,if([Age (Days)] > 30, '30-90'

,'<30'

))

I assigned the colors in the pallate order. If you want to get more specific you can use the background color attribute.

Also, I think your set modifier shoule be:

Status-={'Closed'}

See attached.

-Rob

http://masterssummit.com

View solution in original post

Sokkorn
Master
Master

Hi Jeffrey,

Check my attached file.

Regards,

Sokkorn

jmonroe918
Creator II
Creator II
Author

Rob:

Thanks for the solution. I tried to use the Background Color attribute with both statements you see below, but neither worked. I tried them in both the Dimension at the calculation and at the expression. Are they incorrect?

=if([Age (Days)] = '<30', green(),
if([Age (Days)] = '>90', red(),
yellow()
))

and

=if([Age (Days)] <30, green(),
if([Age (Days)]  >90, red(),
yellow()
))

Jeff

jmonroe918
Creator II
Creator II
Author

Sokkorn:

Thank you, too for the solution.

Regards

Jeff

rwunderlich

Hi Jeff,

The Background Color attribute belongs in the expression. Because you are in an aggregation, you need to include an aggregation function like avg in the test:

=if(avg([Age (Days)]) > 90, red()

,if(avg([Age (Days)]) > 30, yellow()

,green()

))

-Rob

http://masterssummit.com

jmonroe918
Creator II
Creator II
Author

Rob:

Thanks again. Makes sense. I also used the "rgb()" expression to get the red, green and yellow I wanted.

Jeff