topic Re: Color shading in Pivot table in QlikView

Color shading in Pivot table

Anonymous — Thu, 21 Dec 2017 07:02:59 GMT

Hi,

How can I achieve the attached pivot table.

I want to shade the background color as blue and white as per the country and the product alternatively.

But it should be dynamic.

if product count is increasing at later point of time then the shading will also go with it.

Please help me out with the solution.

Re: Color shading in Pivot table

Anil_Babu_Samineni — Thu, 21 Dec 2017 07:06:13 GMT

May be use ARGB() instead RGB()

Using RGB() and ARGB() to define colors

Re: Color shading in Pivot table

Anonymous — Thu, 21 Dec 2017 07:22:41 GMT

Its not working.

Initially i was using the below expression

=IF(EVEN(SubField(RowNo(TOTAL)/8.1,'.',1)),RGB(179,234,255))

which is a hardcoded one because when my product list is increasing then the i have to modify expression again.

And if one country is having 7 products and another is having 8 products then also my expression is going wrong.

Later I have used the below expression but with this expression all country products and contract rate is going blue.

I want athe shading altrenatively

=if(count(DISTINCT([Product Type])),RGB(179,234,255))

Re: Color shading in Pivot table

Anil_Babu_Samineni — Thu, 21 Dec 2017 07:31:37 GMT

Your last expression working? I don't think so..

May be try this way?

Color(FieldIndex('Product Type',Count([Product Type])))

Note - If not working, can you provide image?

Re: Color shading in Pivot table

uacg0009 — Thu, 21 Dec 2017 08:15:22 GMT

Hi Nikita,

I found a way but it's a little complex, I think maybe there is better way to do this, I'm trying.

Now the method is :

You need to create another table and only have 2 columns. 1:Country 2. Rowno()

Then you use the field of rowno to change the background color. But your sort needs to be the same with the field.

Sample below.

Hope it's helpful.

Thanks.

Aiolos

Re: Color shading in Pivot table

Anonymous — Wed, 23 May 2018 10:07:36 GMT

Hi Aiolos,

I tried to open the QV file which you have attached here but it is not opening.

Can you please send me the expression.

Thanks.

Re: Color shading in Pivot table

uacg0009 — Wed, 23 May 2018 10:55:49 GMT

Hi Nikita,

In my sample, in background color of expression, the expression is =if(Even(test),Green(),Blue())

test is another column

Thanks.

Aiolos

Re: Color shading in Pivot table

Anonymous — Wed, 23 May 2018 13:25:35 GMT

Hi Aiolos,

I am still not getting the desired result.All rows are getting blue colored.

Below is the expected result.

Desc: I have 2 dimension viz Country and Product.For 1 country I have 4 product while for other 4 or 5 or 2.

My color shading should happen accordingly and alternatively for country.

As of now I am using the below expression in my background

IF(EVEN(SubField(RowNo(TOTAL)/4.1,'.',1)),RGB(226,226,226))

Which is a hardcoded and working wrongly.

If I am using the above expression then for first country it is working fine but for others it is happening as below.

Please help me out with any generic solution.

Thanking you in advance.

Thanks,

Nikita

Re: Color shading in Pivot table

uacg0009 — Thu, 24 May 2018 01:54:57 GMT

Hi Nikita,

You can see my screenshot below:

Hope this is what you want.

In my pivot table, you can see dimension are "Month" and "Companies name",

expression is sum([Pickup Number])

The color control is in

The definition is =if(Even(aggr(NODISTINCT RowNo(),Month)),White(),lightBlue())

The sort of Month is also very important in my solution.

Please try, any questions let me know.

Thanks.

Aiolos

Re: Color shading in Pivot table

Anonymous — Thu, 24 May 2018 08:52:04 GMT

Hello,

look here :

http://www.qlikfix.com/2010/11/25/custom-formatting-table-cells/

(I used the way via User Preferences (“Settings” -> “User Preferences”) and enabling the “Always Show Design Menu Items” option on the “Design”)

Re: Color shading in Pivot table

Anonymous — Thu, 24 May 2018 13:43:05 GMT

Hi Aiolos,

Thank you so much for your solution.

It is working when I am giving the dimension country..

But one more situation is coming when I am giving a condition in my dimension.

When I am giving If(Region='APAC',Country) for Country dimension then the color shading is happening for 2 consecutive

countries.

It will be great if you can help me with this too.

Thanks,

Nikita

Re: Color shading in Pivot table

uacg0009 — Fri, 25 May 2018 02:52:56 GMT

Hi Nikita,

I think if you use that expression to be a dimension, then you can change your aggr function at the same time

=if(Even(aggr(NODISTINCT RowNo(),If(Region='APAC',Country))),White(),lightBlue())

Please try.

Thanks.

Aiolos

Re: Color shading in Pivot table

Anonymous — Fri, 25 May 2018 07:05:56 GMT

Hi Aiolos,

When I am changing my aggr function then whole column is getting shaded.

=if(Even(aggr(NODISTINCT RowNo(),If(Region='APAC',Country))),White(),lightBlue())

While with normal aggr funtion for the first 4 countries it is taking 2 countries alternatively. But for rest countries its working fine.

=if(Even(aggr(NODISTINCT RowNo(),Country)),White(),lightBlue())

I am not getting where it is going wrong.

Please help if any possible solution.

Thanks,

Nikita

Re: Color shading in Pivot table

uacg0009 — Fri, 25 May 2018 07:50:22 GMT

Hi Nikita,

If you are glad to solve this in back-end script, I thought of a idea.

You can create another column maybe called "APAC_Country" in the back-end.

The logic is just If(Region='APAC',Country)

And then you can use this column to be the dimension and also change the aggr function to

=if(Even(aggr(NODISTINCT RowNo(),APAC_Country)),White(),lightBlue())

I tried in my script, it works.

And also please remember the sort is important.

Thanks.

Aiolos

Re: Color shading in Pivot table

Anonymous — Fri, 01 Jun 2018 12:49:09 GMT

Hi Aiolos,

I got stuck in one more place.

Please find the below image.

Expr: aggr(NODISTINCT RowNo(),EMEIA_Country)

Desc: UK value is taking 1 while Austria is taking 2 because of which my last two country is going in the same color as both are odd numbers i.e. 9 and 1.

I have sorted Country from A to Z and I want Austria value to be taken as 1.

Please let me know If you have any idea on it .

Many Thanks,

Nikita

Re: Color shading in Pivot table

uacg0009 — Mon, 04 Jun 2018 02:01:37 GMT

Hi Nikita,

Are you using qlikview 12? because in qlikview 12, there is a new function, the aggr function can sort.

Thanks

Aiolos

Re: Color shading in Pivot table

Anonymous — Mon, 04 Jun 2018 06:25:56 GMT

Hi Aiolos,

Yes I am using Qlikview12.

If any possibility to change this?

Thanks,

Nikita

Re: Color shading in Pivot table

uacg0009 — Mon, 04 Jun 2018 06:42:49 GMT

Hi Nikita,

Please try below expression :

aggr(NODISTINCT RowNo(), (EMEIA_Country,(TEXT, ASCENDING)))

Thanks.

Aiolos

Re: Color shading in Pivot table

Anonymous — Thu, 07 Jun 2018 07:37:56 GMT

Thanks a ton Aiolos.

Now everything is working as expected.!!

Re: Color shading in Pivot table

uacg0009 — Fri, 08 Jun 2018 03:04:17 GMT

That's good~

Glad to help you.