Exclude Dimension from a chart

[Anonymous]

Hi All,

I have been having trouble excluding some criteria from a bar and line chart.  I only want to show 2 of the 3 data options.  I have this as a dimension:

=if([Opportunity Type]<>'Renewal',[Opportunity Type])

This puts in a "-" for the data in the legend, because I can't get the set analysis right in the expression, which currently is:

=SUM({$<Won_Flag={1}>} [MRR])

I want to sum the MRR when the Won_Flag is = 1, and exclude [Opportunity Type] of 'Renewal'.

[reshmakala · Accepted solution]

Can you try this?

=SUM({$<Won_Flag={1}, [Opportunity Type] = {*}-{Renewal}>}MRR)

[Anonymous]

That works!  Thanks!

I swear one day I will get the full scale of set analysis in Qlik. 

[sunny_talwar]

Have you tried using Suppress the null option on the dimension tab?

Best,

S

[Anonymous]

In this case, the values weren't null, they were just very low.  It did bring up a discussion on processes, but it's historical data so we aren't going to revisit

[sunny_talwar]

Well but you forced one type of observation (Renewal) to be null for Opportunity Type dimension. When you select the suppress when value is null, you should not see '-' in your chart anymore. Give it a try, it may or may not work, but no harm in giving it a shot.

Update: You won't have to add the Set Analysis constraint, it should work without adding the constraint.

Best,

S