topic Re: Background color in pivot table in QlikView

Background color in pivot table

Thu, 09 Apr 2015 17:44:23 GMT

Hi everyone,

I have a pivot table like this one:

This pivot table is an aggregation of a big amount of rows(not in the example file).

The table is working fine when I don't set the expression for the background color. It's fast. Once I put one of these expressions, the ui becomes busy until is able to highlight the cells and it uses a lot of extra memory(without the color expression is using 180 mb and with the color expression, more that 4 GB of RAM!):

if(Category = 'Revenue', RGB(150,220,100), if(Category = 'Sales Forecast', RGB(150,150,150), Null()))

if(only(Category) = 'Revenue', RGB(150,220,100), if(only(Category) = 'Sales Forecast', RGB(150,150,150), Null()))

It seems that Qlikview es evaluating the color expression for each value.

Is there a way to highlight the cells depending on the end result and not for each value?

Thanks in advance!

Daniel

Re: Background color in pivot table

sunny_talwar — Thu, 09 Apr 2015 17:56:02 GMT

Not sure what you mean when you say this: Is there a way to highlight the cells depending on the end result and not for each value? Can you give an example?

Best,

Sunny

Re: Background color in pivot table

Thu, 09 Apr 2015 18:01:59 GMT

Hi Daniel Miguel-Perez,

Could you please attach the file. So that it will be easy to give u exact

and correct result/output u want.

Regards,

Deepac Bangarwa

On Apr 9, 2015 11:14 PM, "Daniel Miguel-Perez" <qcwebmaster@qlikview.com>

Re: Background color in pivot table

Thu, 09 Apr 2015 18:02:43 GMT

Hi Sunny,

I mean based on the final result of the table aggregation. For example, in the image I posted I have 3 Categories * 5 Months -> 15 cells. I want to be able to highlight the cells based on these 15 cells and it looks like Qlikview is evaluating the color expression for each row in my fact table, which is a huge amount.

Thanks,

Daniel

Re: Background color in pivot table

sunny_talwar — Thu, 09 Apr 2015 18:07:03 GMT

Are you looking for something like this? PFA

Used the following condition to get this:

if(Column(1) < 1.5, RGB(150,220,100), if(Column(1) < 2, RGB(150,150,150), Null()))

Best,

Sunny

Re: Background color in pivot table

Thu, 09 Apr 2015 18:33:35 GMT

Hi Sunny,

No, I thinks you are coloring based on the measure value.

I want to color the cell if Category Dimension is a certain value.

if(Category = 'Revenue', RGB(150,220,100), if(Category = 'Sales Forecast', RGB(150,150,150), Null()))

Thanks,

Daniel

Re: Background color in pivot table

sunny_talwar — Thu, 09 Apr 2015 18:37:55 GMT

What would that certain value be? You will need to nest the condition for that certain value within your existing if statement expression. Does that make sense?

Best,

Sunny

Re: Background color in pivot table

sunny_talwar — Thu, 09 Apr 2015 18:40:47 GMT

Something like this?

If(Category = 'Revenue', If(Column(1) < 2, RGB(150,220,100)), If(Category = 'Sales Forecast', If(Column(1) < 2, RGB(150,150,150))))

Re: Background color in pivot table

Thu, 09 Apr 2015 18:44:10 GMT

Hi Sunny,

This expression:

if(Category = 'Revenue', RGB(150,220,100), if(Category = 'Sales Forecast', RGB(150,150,150), Null()))

is creating the format that I want, but the issue is with performance that it seems to be evaluating the expression for each value in the Fact table and not for each value in the pivot table.

Best,

Daniel

Re: Background color in pivot table

sunny_talwar — Thu, 09 Apr 2015 18:46:19 GMT

Got it, Sorry for the confusion. So you are trying to make this expression more efficient so that it takes less system resources?

Best,

Sunny

Re: Background color in pivot table

sunny_talwar — Thu, 09 Apr 2015 18:49:40 GMT

Try doing something like this:

This should help the performance

Facts:

LOAD * INLINE [

Category, Month, Amount

Sales Forecast, 201401, 1

Factory Forecast, 201401, 1.1

Revenue, 201401, 1.2

Sales Forecast, 201402, 1.3

Factory Forecast, 201402, 1.4

Revenue, 201402, 1.5

Sales Forecast, 201403, 1.6

Factory Forecast, 201403, 1.7

Revenue, 201403, 1.8

Sales Forecast, 201404, 1.9

Factory Forecast, 201404, 2

Revenue, 201404, 2.1

Sales Forecast, 201405, 2.2

Factory Forecast, 201405, 2.3

Revenue, 201405, 2.4

];

Color:

LOAD * INLINE [

Category, R, G, B

Factory Forecast, 255, 255, 255

Revenue, 150, 220, 100

Sales Forecast, 150, 150, 150

];

Expression for Background Color: RGB(R, G, B)

Best,

Sunny

Re: Background color in pivot table

Thu, 09 Apr 2015 19:18:18 GMT

Hi Sunny,

Performance is the same with this approach. It seems to be evaluating RGB(R,G,B) for each value in the Fact Table even when this info is in the Category table.

Thanks,

Daniel

Re: Background color in pivot table

sunny_talwar — Thu, 09 Apr 2015 19:20:05 GMT

Thinking out loud, what if you join this in the back end (instead of keeping it as a separate table):

Facts:

LOAD * INLINE [

Category, Month, Amount

Sales Forecast, 201401, 1

Factory Forecast, 201401, 1.1

Revenue, 201401, 1.2

Sales Forecast, 201402, 1.3

Factory Forecast, 201402, 1.4

Revenue, 201402, 1.5

Sales Forecast, 201403, 1.6

Factory Forecast, 201403, 1.7

Revenue, 201403, 1.8

Sales Forecast, 201404, 1.9

Factory Forecast, 201404, 2

Revenue, 201404, 2.1

Sales Forecast, 201405, 2.2

Factory Forecast, 201405, 2.3

Revenue, 201405, 2.4

];

Join (Facts)

LOAD * INLINE [

Category, R, G, B

Factory Forecast, 255, 255, 255

Revenue, 150, 220, 100

Sales Forecast, 150, 150, 150

];

Best,

Sunny

Re: Background color in pivot table

Thu, 09 Apr 2015 20:00:38 GMT

Hi Deepac,

I prepared a better example.

Please find attached a similar example with more data. The color expression for the measures are commented, if you uncomment the code you should see a delay to highlight the rows, especially in the second chart .

Best regards,

Daniel

Re: Background color in pivot table

Mon, 13 Apr 2015 22:44:05 GMT

Hi Sunny,

This doesn't improve the performance either.

I posted a file with a more representative example(more data).

Best,

Daniel

Re: Background color in pivot table

sunny_talwar — Tue, 14 Apr 2015 10:08:27 GMT

Hi Daniel

I will have a look at it today and see if I can find a better solution.

Best,

Sunny

Re: Background color in pivot table

Tue, 08 Nov 2016 11:04:25 GMT

Hi Daniel

Have you found a solution for this yet?

I have the same issue.

Kind Regards

Jandre Killian

Re: Background color in pivot table

MK9885 — Tue, 08 Nov 2016 18:53:24 GMT

Have you tired using 'Custom format cell' ?

I do not know if it will consume less memory but it is it will show color only upon selection.

After adding the color expression the size of your file increased to 40+mb but I do not how it will perform at your end.

Right click on any cell> Custom format cell> add custom color (your expression)

Re: Background color in pivot table

Tue, 08 Nov 2016 18:59:08 GMT

Hi Arvind

I wont be able to use Custom Format Cell, Also i tried this without any Calculation.

IE Rgb(255,0,0) as a background color - and uses much more ram then the amount needed to generate the table.

My chart consumes 10GB ram to open - without any selections. Adding the background color on expression uses more than 80GB to generate the same chart with only Rgb(255,0,0) as a background color - so im not performing any calculations. I also tried this on the dimension background color as a work around. Sadly this gave me the same results.

Regards

Jandre

Re: Background color in pivot table

Thu, 10 Nov 2016 23:58:46 GMT

Hi Jandré,

I found a workaround that performs better that the first solution, but I don't know how it will perform with 10GB charts.

Basically I added a new calculation for each value in the Category dimension. In that what I just had to set a fix background color value for each calculation. See the V2 chart in the attached file.

Best regards,

Daniel