Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Pivot Table Conditional Coloring

Hello everyone,

At the moment I have a pivot table that lists out the name of a document, say "X Datasheet", and then lists it's locations, so a 1 in SFDC and a 1 in "X Website". I am working in qlik sense, and I need a function to put in background color that says... If there is a link in SFDC and a link on the website, turn both boxes green. However, 'SFDC link' is not a dimension or measure on its own, so it would have to be something like, when the location equals SFDC and the location equals website, turn both boxes green. So my pivot table looks something like this...Capture.PNG

I would like to write a function that will turn B2 and C2 Green, because they are equal. I am having trouble with the formatting, and it might not be possible because of the data model (as the data model is set up with Asset = X Data sheet, Location = SFDC, next line, Asset = X Datasheet, Location = Website, etc)

Please let me know! At the moment, I have a totals line, which would be equal to 2 in the first line and 1 in the second, and have it set to green when > 1.

7 Replies
shraddha_g
Partner - Master III
Partner - Master III

Share sample app and Data if possible.

Not applicable
Author

Hello,

Sure.

Capture.PNG

This is what my data looks like for two different assets. As you can see, the first Asset is listed in two different locations with two different links, whereas the second asset in row 4 is only found on SFDC. Right now, I have a pivot table set up that looks like this.

Capture 1.PNG\

The 'Check' Column is the Totals column, and I have the entire measure set up like this.

Capture.PNG, if(Count(URL) > 1, 'palegreen') where anywhere that says 2 is highlighted in green, and this only occurs in the totals check column. However,  I would like to take out this column and have just the Salesforce and Qlik.com columns, and have a background color expression that turns both of the SFDC and Qlik.com boxes green in 14 Tales of marketing Insight (because both boxes = 1), and leaves Healthcare Labor Productivity Data Sheet the way it is. Essentially, green boxes to signal that there is a link in both locations.

shraddha_g
Partner - Master III
Partner - Master III

Try in Background color,

Count(Total URL) >1 , 'palegreen')

Not applicable
Author

Hi,

Thank you, but this does not work. I was thinking something that could say,

if([LOCATION] = 'Qlik.com' and [LOCATION] = 'SFDC', 'green')

or

if([Qlik.com Link] >= '1' and [Salesforce Link] >= '1', 'green','yellow')

shraddha_g
Partner - Master III
Partner - Master III

Is lt working?

Not applicable
Author

I had already replied, your function doesn't work. Neither of mine do either. Unfortunately, because of the way the data is set up, 'SFDC' and 'Qlik.com' are not fields, so it is not that simple, as location is the field.

Not applicable
Author

I was also thinking something like this

if(Count({(< [LOCATION] = { 'SFDC' } >* < [LOCATION] = { 'Qlik.com' } >)*}) = 1 'palegreen'))

to indicate that the intersections of the two locations would make it green. I have a similar function in another place to count the number of URLS in that intersection, but I don't know how to apply it or format it.