Restrict dimension values but show all of them

[#1]

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?

[sunny_talwar]

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

[#3]

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

[sunny_talwar]

Would you be able to post a sample?

Best,

Sunny

[sunny_talwar]

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

Best,

Sunny

[bgerchikov]

In Dimension Tab select "Show All Values"

It should help

[#7]

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.

[sunny_talwar · Accepted solution]

See if this is somewhat helpful: (PFA)

Best,

Sunny

[bgerchikov]

Kevin,

Show all Values supposed to work for the calculated dimensions.

Another option is using List Of Values as a dimension.

[#10]

Thanks, Sunny, that's a great hack!