Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
MVP & Luminary
MVP & Luminary

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
Highlighted
MVP & Luminary
MVP & Luminary

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

Highlighted
Master
Master

Hi Jeffrey,

Check my attached file.

Regards,

Sokkorn

Highlighted
Creator II
Creator II

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

Highlighted
Creator II
Creator II

Sokkorn:

Thank you, too for the solution.

Regards

Jeff

Highlighted
MVP & Luminary
MVP & Luminary

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

Highlighted
Creator II
Creator II

Rob:

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

Jeff