Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
haneeshmarella
Creator II
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
neelamsaroha157
Specialist II
Specialist II

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

haneeshmarella
Creator II
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.

haneeshmarella
Creator II
Creator II
Author

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

vishsaggi
Champion III
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?

vishsaggi
Champion III
Champion III

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

see below?

Capture.PNG

haneeshmarella
Creator II
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%')

haneeshmarella
Creator II
Creator II
Author

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

vishsaggi
Champion III
Champion III

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

haneeshmarella
Creator II
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())

vishsaggi
Champion III
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.