topic Re: Dynamic Color only for Column not Sum in QlikView

Dynamic Color only for Column not Sum

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

Hello All,

I would like to have Color in my Pivot Table when the number is greater then a particular value. But, the Color of the column Total must remain default.

Example:

Sales
30
40
50
10
12
14
16
89
83
Total: 344

Here, i would like to Color all the Columns above 40 with Yellow but, the Total must remain the same.

Thanks!!

Re: Dynamic Color only for Column not Sum

YoussefBelloum — Fri, 17 Nov 2017 10:03:25 GMT

=if( sum(Sales)<>sum(TOTAL Sales) and sum(Sales) > YOUR_VALUE, rgb(YOUR_COLOR))

Re: Dynamic Color only for Column not Sum

stabben23 — Fri, 17 Nov 2017 10:16:19 GMT

This is could also be implemented in Background color in Your Expression.

Dimensionality()=1 if you have 1 Dimension, Dimensionality()=2 if you have 2 aso...

=if(Dimensionality()=1 and sum(Cost) > 40,rgb(255,255,0))

Re: Dynamic Color only for Column not Sum

Akina0929 — Fri, 17 Nov 2017 10:30:35 GMT

hi ,

enable design grid>>

right click on pivot table>>

custom cell format>>

text color

exp--------- if(sales>.. ,red()).

best Regards

Anji

Re: Dynamic Color only for Column not Sum

psk180590 — Fri, 17 Nov 2017 11:38:09 GMT

Thanks All for the quick suggestions.

But, somehow i'm not arriving at my goal. Have attached a sample app. Here in the Column Quantity i would like to have all above 50 in Yellow.

Re: Dynamic Color only for Column not Sum

Anil_Babu_Samineni — Fri, 17 Nov 2017 11:43:20 GMT

You are not expecting this?

Re: Dynamic Color only for Column not Sum

psk180590 — Fri, 17 Nov 2017 11:44:23 GMT

Yes, this is what i'm looking to achieve.

Re: Dynamic Color only for Column not Sum

Anil_Babu_Samineni — Fri, 17 Nov 2017 11:50:53 GMT

Then both of the above solution should work for BG color of expansion from expression

=if(Sum(Quantity)<>Sum(TOTAL Quantity) and Sum(Quantity) > 50, Yellow())

=if(Dimensionality()=1 and Sum(Quantity) > 50,rgb(255,255,0))

Re: Dynamic Color only for Column not Sum

psk180590 — Fri, 17 Nov 2017 12:02:04 GMT

The problem is i'm also getting the color on the Total column

(I have color code for >20 here)

As in the below Screen shot(i masked out my Product Name) and also in the Row Total it is turning out Yellow.

I don't want color in the Total column.

Re: Dynamic Color only for Column not Sum

Anil_Babu_Samineni — Fri, 17 Nov 2017 12:05:05 GMT

Do you used Pick(Dim, Field, 'Static1','Static2') as Dimension ??

Re: Dynamic Color only for Column not Sum

psk180590 — Fri, 17 Nov 2017 12:07:06 GMT

No, i have total 4 straight Dimensions from the data model

Re: Dynamic Color only for Column not Sum

Anil_Babu_Samineni — Fri, 17 Nov 2017 12:09:17 GMT

Will you able to provide one more sample. But Try a luck

=if(Match(Dimensionality(), 1, 2, 3, 4) and Sum(Quantity) > 50,rgb(255,255,0))

Re: Dynamic Color only for Column not Sum

psk180590 — Fri, 17 Nov 2017 12:10:25 GMT

See attached, it will give a better idea of my problem

Re: Dynamic Color only for Column not Sum

YoussefBelloum — Fri, 17 Nov 2017 13:05:03 GMT

Here it is :

=if(RowNo()<>0 and ColumnNo()<>0 and Sum(Quantity) <> Sum(TOTAL Quantity) and sum(Quantity) > 50, Yellow())

Re: Dynamic Color only for Column not Sum

Anil_Babu_Samineni — Fri, 17 Nov 2017 13:06:42 GMT

PFA

Re: Dynamic Color only for Column not Sum

psk180590 — Fri, 17 Nov 2017 13:29:47 GMT

Thanks, almost perfect.

Say in case i have 3 dimensions(in the sample there is only 1). How can i change the Set Analysis?

Re: Dynamic Color only for Column not Sum

YoussefBelloum — Fri, 17 Nov 2017 13:38:42 GMT

don't need to change !

Re: Dynamic Color only for Column not Sum

psk180590 — Fri, 17 Nov 2017 13:43:44 GMT

See the attached app once.

Re: Dynamic Color only for Column not Sum

YoussefBelloum — Fri, 17 Nov 2017 16:21:11 GMT

I think the coloring is good even here, it looks wrong because you have pivoted the Dimension Field !

Re: Dynamic Color only for Column not Sum

psk180590 — Mon, 20 Nov 2017 14:35:18 GMT

I actually want the colors to remain out of the Sum columns even after pivoting.

I probably think that is not possible here, or if some expert could throw more inputs here