Background color in pivot table

[#1]

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

or

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

[sunny_talwar]

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

[#3]

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

[sunny_talwar]

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

[#5]

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

[#6]

Hi Sunny,

This doesn't improve the performance either.

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

Best,

Daniel

[sunny_talwar]

Hi Daniel

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

Best,

Sunny

[#8]

Hi Daniel

Have you found a solution for this yet?

I have the same issue.

Kind Regards

Jandre Killian

[MK9885]

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)

[#10]

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

[#11]

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