Color Coding Calculated Dimension in Pivot Tables

[haneeshmarella]

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?

[vishsaggi]

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

[haneeshmarella]

I get the same result after removing num.

[vishsaggi]

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]

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

[neelamsaroha157]

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]

[haneeshmarella]

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]

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]

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.