Expression which includes in few Dimensions. Pipeline purposes

[oleg_datsenko]

Hi to all!

I created a pipeline which show me opportunities transmitting from one stage to the next:

1) First bar show an amount of all Open opportunities at the moment(field Sale stage = Prospecting OR Qualificatioin OR SolutionDesign OR DecisionPending). Expression:

sum({$<[Sale stage]={'Prospecting','Qualification','Solution Design/Proposal','Decision Pending'}>}Revenue)

2) Second bar show an amount of opportunities which Sale stage = Qualificatioin OR SolutionDesign OR DecisionPending). Hint: I have exclude Prospecting from this set. Expression:

sum({$<[Sale stage]={'Qualification','Solution Design/Proposal','Decision Pending'}>}Revenue)

3) Third bar show an amount of opportunities which Sale stage = SolutionDesign OR DecisionPending. Prospecting and Qualification excluded. Expression:

sum({$<[Sale stage]={'Solution Design/Proposal','Decision Pending'}>}Revenue)

4) Fourth bar show an amount of opportunities which Sale stage only DecisionPending. Expression:

sum({$<[Sale stage]={'Decision Pending'}>}Revenue)

The issue is I can not click on each bar to subset a list of opportunities which has fallen into respective bar, because... I done these four bars with Expressions, instead of Dimensions.

Can anybody suggest me the way to create these bars clickable?

Source example:

Sale stage, Revenue

Prospective, 100000 // should be displayed in first bar, NOT in second, third or forth

Qualification, 200000 // should be displayed in first AND second bar, NOT in third or forth

Solution Design/Proposal, 80000 // should be displayed in first AND second AND third bar, NOT in forth

Decision Pending, 50000 // should be displayed in ALL bars

One more tip: the chronology of sales stages is 'Prospecting->Qualification->Solution Design/Proposal->Decision Pending'. It means that: revenue of opportunities with Sale stage 'Decision Pending' should be in every previous bar(Prospecting->Qualification->Solution Design/Proposal); revenue of opportunities with Sale stage 'Solution Design/Proposal' should be in every previous bar(Prospecting->Qualification)... and so on.

Hope I describe my issue exhaustively.

Oleg.

[marcus_sommer]

You could try it with a calculated dimension with valuelist:

valuelist('Prospecting','Qualification','Solution Design/Proposal','Decision Pending')

and then your expression:

if(valuelist('Prospecting','Qualification','Solution Design/Proposal','Decision Pending') = 'Prospecting',

     sum({$<[Sale stage]={'Prospecting','Qualification','Solution Design/Proposal','Decision Pending'}>}Revenue),

if(valuelist('Prospecting','Qualification','Solution Design/Proposal','Decision Pending') = 'Qualification',

      sum({$<[Sale stage]={'Qualification','Solution Design/Proposal','Decision Pending'}>}Revenue),

...

See also: ValueList() – For those tricky situations

- Marcus

[oleg_datsenko]

Hi Marcus,

Thanks for reply!

Interesting idea, but it doesn't finaly fix my issue. As previously, bars aren't clickable...

Oleg