topic Re: Creating a legend via macro or expression? in QlikView

Creating a legend via macro or expression?

Thu, 19 Jul 2012 18:22:47 GMT

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

Re: Creating a legend via macro or expression?

Thu, 19 Jul 2012 18:28:21 GMT

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

Re: Creating a legend via macro or expression?

Thu, 19 Jul 2012 18:32:18 GMT

use expression.

it keeps code simpler and without macro security issues

Re: Creating a legend via macro or expression?

Thu, 19 Jul 2012 20:23:22 GMT

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

Re: Creating a legend via macro or expression?

Nicole-Smith — Fri, 20 Jul 2012 16:25:59 GMT

I believe this is what you want...

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

Re: Creating a legend via macro or expression?

Fri, 20 Jul 2012 17:19:08 GMT

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"

Re: Creating a legend via macro or expression?

Nicole-Smith — Mon, 23 Jul 2012 12:27:00 GMT

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.

Re: Creating a legend via macro or expression?

Mon, 23 Jul 2012 14:24:28 GMT

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.

Re: Creating a legend via macro or expression?

Nicole-Smith — Mon, 23 Jul 2012 18:47:52 GMT

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!

Re: Creating a legend via macro or expression?

Tue, 24 Jul 2012 21:00:16 GMT

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

Re: Creating a legend via macro or expression?

Nicole-Smith — Wed, 25 Jul 2012 12:50:57 GMT

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

Re: Creating a legend via macro or expression?

Wed, 25 Jul 2012 21:30:59 GMT

ahhhh thanks soo much nicole, clearly I was way off with the if statements

Thanks so much for your help, much appreciated