topic Re: Color Coding Calculated Dimension in QlikView

Color Coding Calculated Dimension in Pivot Tables

haneeshmarella — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Color Coding Calculated Dimension

vishsaggi — Mon, 20 Aug 2018 19:58:55 GMT

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

Re: Color Coding Calculated Dimension

haneeshmarella — Mon, 20 Aug 2018 20:02:06 GMT

I get the same result after removing num.

Re: Color Coding Calculated Dimension

vishsaggi — Mon, 20 Aug 2018 20:08:02 GMT

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 .

Re: Color Coding Calculated Dimension

haneeshmarella — Mon, 20 Aug 2018 20:12:32 GMT

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

Re: Color Coding Calculated Dimension

neelamsaroha157 — Mon, 20 Aug 2018 20:26:53 GMT

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

Re: Color Coding Calculated Dimension

neelamsaroha157 — Mon, 20 Aug 2018 20:29:20 GMT

Re: Color Coding Calculated Dimension

haneeshmarella — Mon, 20 Aug 2018 20:57:46 GMT

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.

Re: Color Coding Calculated Dimension

neelamsaroha157 — Mon, 20 Aug 2018 21:01:45 GMT

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.

Re: Color Coding Calculated Dimension

vishsaggi — Mon, 20 Aug 2018 21:08:27 GMT

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.

Re: Color Coding Calculated Dimension

neelamsaroha157 — Mon, 20 Aug 2018 21:14:04 GMT

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

Re: Color Coding Calculated Dimension

haneeshmarella — Mon, 20 Aug 2018 21:18:24 GMT

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.

Re: Color Coding Calculated Dimension

haneeshmarella — Mon, 20 Aug 2018 21:18:50 GMT

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

Re: Color Coding Calculated Dimension

vishsaggi — Mon, 20 Aug 2018 21:26:49 GMT

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?

Re: Color Coding Calculated Dimension

vishsaggi — Mon, 20 Aug 2018 21:29:57 GMT

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

see below?

Re: Color Coding Calculated Dimension

haneeshmarella — Mon, 20 Aug 2018 21:31:43 GMT

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%')

Re: Color Coding Calculated Dimension

haneeshmarella — Mon, 20 Aug 2018 21:32:44 GMT

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

Re: Color Coding Calculated Dimension

vishsaggi — Tue, 21 Aug 2018 02:49:45 GMT

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

Re: Color Coding Calculated Dimension

haneeshmarella — Tue, 21 Aug 2018 13:16:13 GMT

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())

Re: Color Coding Calculated Dimension in Pivot Tables

vishsaggi — Wed, 22 Aug 2018 19:00:46 GMT

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 stalwar1‌ swuehl‌ rwunderlich‌ can give some help on this.