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

May be because you are converting the value using Num with %ge it is always going to red. remove Num function and try.

haneeshmarella
Creator II
Creator II
Author

I get the same result after removing num.

vishsaggi
Champion III
Champion III

Put that expression in a text object select something in your dimension then see if that value is < or > than 1. I believe you are comparing %ges with normal integer 1. Can you share some sample data you have in that calculated dimension .

haneeshmarella
Creator II
Creator II
Author

Please see columns 'Carton Efficiency' & 'Pieces Efficiency' which are calculated dimensions.

neelamsaroha157
Specialist II
Specialist II

You are Using Hours in your expression which is null and you'll need to put aggregation for the Hours field as well -

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

but this expression is not resulting any data because there is no value for the 'HOURS' field

neelamsaroha157
Specialist II
Specialist II

Capture12.PNG

haneeshmarella
Creator II
Creator II
Author

Neelam, there is not data connected so it is giving you NULL. If you open only the workbook I gave you, you should have the table to play around with.

neelamsaroha157
Specialist II
Specialist II

I opened the file that you gave me, the expression you are using for the calculated dimension,'Carton efficiency', you are diving your expression by the field 'HOURS'

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

But this field doesn't have any value and hence no result in your calculated dimension.

vishsaggi
Champion III
Champion III

Yes, for that date selection it does not have any values. If you change the date it calculates. But background color is not populating. Not sure why.