Color shading in Pivot table

[Anonymous]

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.

[Anonymous]

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

[uacg0009]

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

[Anonymous]

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

[uacg0009]

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

[Anonymous]

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

[uacg0009]

Hi Nikita,

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

Thanks

Aiolos

[Anonymous]

Hi Aiolos,

Yes I am using Qlikview12.

If any possibility to change this?

Thanks,

Nikita

[uacg0009]

Hi Nikita,

Please try below expression :

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

Thanks.

Aiolos

[Anonymous]

Thanks a ton Aiolos.

Now everything is working as expected.!!

[uacg0009]

That's good~

Glad to help you.