topic Re: Custom Format Cell when a cell contains certain text in QlikView

Custom Format Cell when a cell contains certain text

Tue, 22 Nov 2016 16:52:21 GMT

Hi all,

I have a problem which could be solved with a function that doesn't exist, but am hoping somebody can work out a work-a-round to help me out with this:

I have a straight table with many dimensions but am trying to format the background colour of some of the cells in those columns. I want to highlight cells that contain a specific symbol in the text ('>'), but cant find a way of doing it in a formula that i can just apply to all dimensions as i need to reference the name of the dimension that corresponds to the cells in that column in any formula I can find. And, after a while of searching, there is no function that calls the name of the dimension to apply to a formula.

I'd love to be able to do a =if(SubStringCount(ThisDimension(), '>'), vGreen, vWhite) or something...

So other than going into the background colour section of the collapsed menu of each dimension in the chart properties, and writing 70-odd individual formulae asking it to highlight anything containing a '>', is there a way, maybe using the custom format cell option (that has an 'apply to all dimensions' tick box), that I can just highlight any cell containing a '>'? Or some sort of formula work-a-round that bypasses the need to reference the dimension name?

Hope all that makes sense!

Thanks in advance,

Lewis

Re: Custom Format Cell when a cell contains certain text

marcus_sommer — Wed, 23 Nov 2016 12:46:29 GMT

Unfortunately such functionalities aren't available and you will need to do it manually.

- Marcus

Re: Custom Format Cell when a cell contains certain text

adamdavi3s — Wed, 23 Nov 2016 13:01:24 GMT

do vote this up though!

https://community.qlik.com/ideas/2830

Re: Custom Format Cell when a cell contains certain text

vinieme12 — Wed, 23 Nov 2016 13:08:26 GMT

have you tried

=if(FindOneOf(Fieldname,'>')>0,red(),green()) ,

You will need to do this for all expressions/dimensions

Re: Custom Format Cell when a cell contains certain text

adamdavi3s — Wed, 23 Nov 2016 13:11:17 GMT

This still requires the user to do this for every dimension?

Re: Custom Format Cell when a cell contains certain text

vinieme12 — Wed, 23 Nov 2016 14:47:48 GMT

The below macro will allow you to add background expression for all dimensions

SUB coloralldimension()

set chart = ActiveDocument.GetSheetObject("CH01") ' <<Use Your chart object ID here

set cp = chart.GetProperties

n = cp.Expressions.Count

for i = 0 to (n-1)

set fld = chart.getfield(i)

set bce = cp.Dimensions(i).AttributeExpressions.BkgColorExp

bce.Definition.v = "=if(FindOneOf(" & fld.Name &",'>')>0,red(),green())"

chart.SetProperties cp

END SUB

Re: Custom Format Cell when a cell contains certain text

vinieme12 — Wed, 23 Nov 2016 17:51:04 GMT

lewis.vaughan does this work for you!