topic Re: Restrict dimension values but show all of them in QlikView

Restrict dimension values but show all of them

Wed, 15 Apr 2015 15:31:22 GMT

We use a field "Stage" to track where a sale is from start to finish. There are about 30 different values for Stage, but the main five are

00 - Lead, 20 - Sales Opp, 40 - Discovery, 60 - Solution, 80 - Forecast Sale.

My dimension is:

If(mixmatch(left(STAGE,2),'00','20','40','60','80')>=1, STAGE, Null()),

with "Suppress if value is null" selected. Works great, if the rep has a sale at every stage.(the only expression is "Sum(Forecast)")

However, if a rep has no opportunities at, say, 40, that value doesn't show up in the chart, eg:

I've unchecked "Suppress Zero Values" and "Suppress Missing" on the Presentation tab. Is there any way to show the missing stages with a total of $0?

Re: Restrict dimension values but show all of them

sunny_talwar — Wed, 15 Apr 2015 15:42:59 GMT

Try this may be as your expression:

=Sum({<STAGE = {'00 - Lead', '20 - Sales Opp', '40 - Discovery', '60 - Solution', '80 - Forecast'}>} Forecast)

and use STAGE (Complete list of STAGE) as dimension and don't suppress the null.

HTH

Best,

Sunny

Re: Restrict dimension values but show all of them

Wed, 15 Apr 2015 15:50:17 GMT

Nope, shows all the values in the dimension. But thanks for the suggestion.

Re: Restrict dimension values but show all of them

sunny_talwar — Wed, 15 Apr 2015 15:53:25 GMT

Would you be able to post a sample?

Best,

Sunny

Re: Restrict dimension values but show all of them

sunny_talwar — Wed, 15 Apr 2015 15:59:24 GMT

You are right, it doesn't work. Just tested it out. Will try another method.

Best,

Sunny

Re: Restrict dimension values but show all of them

bgerchikov — Wed, 15 Apr 2015 17:50:10 GMT

In Dimension Tab select "Show All Values"

It should help

Re: Restrict dimension values but show all of them

Wed, 15 Apr 2015 17:55:42 GMT

Sorry, Boris, I explicitly do NOT want to show all values, just five. I tried setting the unwanted to null, as in my first post, but that didn't work.

Then I thought, create a flag in the script (ACTIVE) if the STAGE is one of the five I want, soI tried:

If(Active, Stage)

but for a calculated dimension, it appears you CANNOT select "show all values". Same result as in my first post.

Re: Restrict dimension values but show all of them

sunny_talwar — Wed, 15 Apr 2015 18:01:00 GMT

See if this is somewhat helpful: (PFA)

Best,

Sunny

Re: Restrict dimension values but show all of them

bgerchikov — Wed, 15 Apr 2015 18:05:58 GMT

Kevin,

Show all Values supposed to work for the calculated dimensions.

Another option is using List Of Values as a dimension.

Re: Restrict dimension values but show all of them

Wed, 15 Apr 2015 18:06:48 GMT

Thanks, Sunny, that's a great hack!

Re: Restrict dimension values but show all of them

sunny_talwar — Wed, 15 Apr 2015 18:08:27 GMT

Hahahaha ya