# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save \$300 before February 6: REGISTER NOW!
cancel
Showing results for
Did you mean:
Creator II

## Color Coding Calculated Dimension in Pivot Tables

Hi guys,

I have a dimension with the calculation which gives result in terms of %

=num(aggr(Count(distinct CASE#),DATE,USER_NAME)/(alt(HOURS,0)*29),'##0.0%')

When I try to color code it in background color,

IF(num(aggr(Count(distinct CASE#),DATE,USER_NAME)/(alt(HOURS,0)*29),'##0.0%')>=1, green(), lightred())

It makes the entire column red. Which ever color I put on the 'else' condition above, it gives the color to the entire column. What am I doing wrong?

19 Replies
Specialist II

I didn't realize there was a date selection ..

Creator II
Author

Oh gotcha! SOrry, thats because not all dates have hours. It is set to filter to latest week automatically. Please filter to week of 8/13 and the hours will appear.

Creator II
Author

Yeah, tried different ways but not able to figure it out.

Champion III

BTW where and why you are using this expr in background for Carton Efficiency? And why you are multiplying with 110, when i am adding this expr as not calc dimension i am getting - dashes?

Champion III

Ahh. man you do not have a value ON_LINE  in your OFF_ON_LINE field dimension.

see below?

Creator II
Author

Oops, gave you the wrong workbook. But similar formula for this one too, use this for the dimension calculation

=num(aggr(Count(distinct CASE#),DATE,USER_NAME)/(HOURS*29),'##0.0%')

Creator II
Author

Multiplying with 110 as thats the target per hour. Basically its the total number divided by hours*target per hour.

Champion III

‌uuse this for dimension call or for background color expr?

Creator II
Author

Dimension =num(aggr(Count(distinct CASE#),DATE,USER_NAME)/(HOURS*29),'##0.0%')

Background Expr

=IF(num(aggr(Count(distinct CASE#),DATE,USER_NAME)/(HOURS*29),'##0.0%')>=1, green(),lightred())

Champion III

There are nulls coming in few of your dimension fields. And when you try to take up DATE_TIME_CST in the pivot it is disregarding the background colors. Not sure if this is a bug or something else needs to be done. May be stalwar1swuehlrwunderlich‌ can give some help on this.

Community Browser