Set Analysis - Including a specific value regardless of selection

[Anonymous]

Hi Experts,

This is how my data looks like:

ID_primary	ID_a	ID_b	Data
ABC		ABC	50
ABC	DEF	DEF	100
ABC	LMN	LMN	20
ABC	XYZ	XYZ	30
DEF		DEF	20
DEF	ABC	ABC	50
DEF	GHI	GHI	80

Notice the rows where ID_b = ID_primary. Also notice that where ID_b=ID_primary, ID_a=NULL. These may come in handy in understanding what I'm trying to achieve.

I then have a simple chart where Dimension is ID_b and Expression is Sum of Data.

User first selects a distinct ID_primary. For example user selects ABC. Chart will show ABC, DEF, LMN, XYZ.

User will then select one or many values from ID_a. For example, user selects LMN, XYZ. Chart will then update to show LMN, XYZ.

Objective: Regardless of what user selects from ID_a, I want ABC to show up on the chart. Basically I want to always show on the chart where ID_b = ID_primary. So if for example, user selects ID_primary=ABC and then selects ID_a=LMN,XYZ, I want the chart to show ABC,LMN,XYZ.

I have tried using set analysis on the chart expression:

sum({$+<ID_b=ID_primary>}Data)

The above does not work.

Since all rows where ID_b=ID_primary have a NULL value for ID_a, I then tried this:

sum({$+<ID_a=''>}Data)

The above did not work either.

Any suggestions? Attached QVW as example. Thanks!

[sunny_talwar · Accepted solution]

Try this

=Sum({<ID_b = p(ID_primary)+p(ID_a), ID_a>}Data)

[Anil_Babu_Samineni]

Like this?

[Anil_Babu_Samineni]

So Fast

[sunny_talwar]