Pivot Table - Dimension Column color

[el_lobo]

Hi all

I've got a pivot table with two dimensions (Region in rows, Year in columns) and and 3 measures (sum(sales), sum(purchaseosts) and sum(sales)-sum(purchasecosts))

Now I want grey as background-color of all cells in columns of odd year numbers.

So I thought I go to cell format (in design mode) and enter a formula  such as if(odd(year), rgb(200,200,200), rgb(255,255,255)) for the cell background property. But when I click ok, the setting jumps back to a static background color definition...

Now; 2nd guess was putting the condition in the expression's background property; that would color only cells with values; NULL-cells remain white... futhermore Qlik will aply the background color formula also in the year's total colum (where it does not make any sense since there's no distinction between years in that column)...

Does anyone have a hint for me before this drives me nuts?? Any help is highly appreciated 😉

[Anil_Babu_Samineni]

@el_lobo Do you have sample QVF that can demonstrate the issue?

[el_lobo]

Hi @Anil_Babu_Samineni & tx for hopping onto this topic!

The attached sample has the showcase

In this Pivot I added the formula to the expression's background color property (UI is in german, though):

Now Client E has no data for 2023 -> no background color

Screenshot for the 2nd guess I thought would do the trick -> switch to design view mode, right click pivot table and select cell format -> upon clicking 'background color' ("Hintergrundfarbe") there's a input field for a formula. However, whatever I enter there is gone after clicking OK:

So how would I define custom colors for a dimension ('year', in this case)?

[EDIT: typo]

[Anil_Babu_Samineni]

@el_lobo Apologies for the late, So now your expectation for 2023, You need the same color for Client - E, as well?

For second one, I don't see the expression is away after clicking OK?

[marcus_sommer]

You may avoid this kind of struggling by replacing the NULL with the real value of ZERO. Depending on your data-model, data-set and requirements you may reach it with an approach like:

rangesum(sum(sales), sum({$} 0))

or by populating the missing data within the data-model.

[el_lobo]

Hi all & tx for your involvment

...tried with rangesum - looked promising; however, didn't work. As a dirty workaround I add a very tiny small number: sum(sales)+0.0000000000000000000000001

In the presentation tab I checked: fill empty and remove NULL

Still wondering if there's no better 'clean' solution to have a column colored...

[marcus_sommer]

NULL means there is NOTHING against any calculation/value could be assigned/related and therefore each kind of coloring will fail. There is only an option to replace the default NULL representation of '-' with any other string which is probably not related to chart-calculation else just a filling in the rendering of the object.

This means you will need workarounds like above already hinted or any kind of data-population.