Solved: Re: Creating a legend via macro or expression? - Qlik Community

Report Inappropriate Content · ‎2012-07-19

Hi

I was wondering if it was possible to create a legend instead of using "Show Legend". Reason being, I have label in my legend that it appear as Null or "-" and I would like to get rid of that. I tried checking "Suppress When Value is Null" but it screws my bar chart. In order to create a legend, would I use expression or build a macro?

Just looking some guidance and direction

Thanks,

Cliff

Nicole-Smith · ‎2012-07-25

Okay, so here is what you need to do for it to work with the listbox.

Keeping the same dimensions/expressions I had in the last post:

Dimension: Drill Down

Expressions:

1. Count ({<Survey = {'Bad'}>}[Number of Calls])

2. Count ({<Survey = {'Good'}>}[Number of Calls])

3. Count ({<Survey = {'Not Rated'}>}[Number of Calls])

Add a background color to each of the dimensions so that the colors stay consistent when something is chosen from the list box:

1. Bad: rgb(255, 83, 83)

2. Good: rgb(106, 154, 71)

3. Not Rated: rgb(75, 138, 243)

Then add a Conditional to each expression:

1. Bad: substringCount(GetFieldSelections(Survey, ',', 1000000000), 'Bad') <> 0

2. Good: substringCount(GetFieldSelections(Survey, ',', 1000000000), 'Good') <> 0

3. Not Rated: substringCount(GetFieldSelections(Survey, ',', 1000000000), 'Not Rated') <> 0

View solution in original post

Report Inappropriate Content · ‎2012-07-19

If you want my advice,you must use macros only when necessary .

Can you post your file ,so i saw its?

Perhaps we can solve the problem of nul in the legend

Marchetto

Report Inappropriate Content · ‎2012-07-19

use expression.

it keeps code simpler and without macro security issues

Report Inappropriate Content · ‎2012-07-19

Hey Marco,

I have uploaded my Qlikview file, NOTE: I used Qlikview Personal Edition to create this file so I hope you able to access it

Thanks for the help

Cliff

Nicole-Smith · ‎2012-07-20

I believe this is what you want...

Checking "Suppress Value When Null" works for me...

Report Inappropriate Content · ‎2012-07-20

Hey Nicole,

Thats what I originally thought too, when I checked "Suppress Value When Null" it makes the Null disappear however it screws up the columns in my chart. They start to overlap when I make a selection...:S I have must checked something to cause the overlapping

I've made an attachment of what it does when i check "Suppress Value when NULL"

Nicole-Smith · ‎2012-07-23

That's really strange. When I check "Suppress Value When Null" on the Survey dimension (not the Drill Down one), it looks like this (which I believe is what you want?):

What version of QlikView are you using? It works for me in both QV 11 SR1 and QV 9 SR 4.

Report Inappropriate Content · ‎2012-07-23

Yes that is how I want the legend to look like. Have you played around with couple of selections in the listbox? I can get the survey to look like that when there no current selections. I am using Qlikview 11 Personal Edition.

Nicole-Smith · ‎2012-07-23

Try this:

Dimension: "Drill Down" (get rid of the Survey dimension)

Expressions:

1. Count ({<Survey = {'Bad'}>}[Number of Calls])

2. Count ({<Survey = {'Good'}>}[Number of Calls])

3. Count ({<Survey = {'Not Rated'}>}[Number of Calls])

The selections work for me when I make the chart that way.

After looking at your data structure, you have a synthetic table and a lot of synthetic keys. This may be causing the original problem. I would suggest you try to rewrite your load script to get rid of any synthetic tables/keys.

I hope this solves your problem!

Report Inappropriate Content · ‎2012-07-24

Thanks Nicole it does kinda help. I am still trying to link the expressions to the my Survey listbox. The chart doesnt dynamically update when I make selections in the survey listbox (for examply only want to look like bad results). I am trying to use if statements in the expression to link the expressions with my survey listbox