# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our May 2021 end-to-end product release from Data Integration to Data Analytics is out! READ DETAILS
cancel
Showing results for
Search instead for
Did you mean:
Contributor II

## Set analysis in a dimension??

Hi all,

I have the following data sets (simplified for this post)

Table 1

ResourceSector

Janet P

Team1
David JTeam2
Peter NTeam1
Sarah PTeam1
Michael HTeam2

Table 2

ResourceSalarySales
Sarah P£33,000£150,000
Donald T£24,000£147,000
Janet P£27,000£72,000
Walter M£26,000£107,000
David J£32,000£96,000
Peter N£29,000£47,000
Donny G£31,000£112,000
Michael H£28,000£137,000
Trisha Y£32,000£92,000
Derek A£33,000£63,000

I'm using table 2 as the basis for a scatter plot, with Resource as the dimension. Table 1 is used elsewhere in the visualisation, but they are linked via the Resource column in the data manager.

Is it possible to only show the resources in the scatter plot that appear in table 1, instead of the full set of table 2? I assumed this could be done by set analysis but don't have the skills just yet to make it work.

Any help would be greatly appreciated.

Many thanks,

Pokes

1 Solution

Accepted Solutions
Partner

Quickest way to do this without modifying your load script would be use a calculated dimension with the below expression:

 =Aggr(MaxString({} Resource),Resource)

The resources who do not appear in table 1 will show up as null on the scatter plot, therefore you must untick the Include null values option on the chart dimension.

8 Replies
Partner

in the script in ur table1 add a flag field as follow:

'Table1' as FlagTable

Now, in ur scatter plot; add this to ur meassure:

sum({<FlagTable={'Table1'}>}Measure)

Partner

Quickest way to do this without modifying your load script would be use a calculated dimension with the below expression:

 =Aggr(MaxString({} Resource),Resource)

The resources who do not appear in table 1 will show up as null on the scatter plot, therefore you must untick the Include null values option on the chart dimension.

Partner

Hi Leo,

You can achieve this by creating flags.

Go to the Table 1 load scrip and add "'Table1' as Flag" in the script and reload.

Now when you create you scatter chart simply put in the 'Flag' in set analysis and it will pick values from the table where your flag is

Ex: sum({<Flag={'Table1'}>}Salary) will calculate Salary for your Resource in Table 1

Regards,

Arjun

Creator III

Try using

sum(\${<Sector = {'*'}>}Measure)

this should only use values for calculation that have a value in Sector, ignoring null values due to connecting both tables.

Regards

Chris

Contributor II
Author

Thanks Omar for the quick response - this works really well.

Contributor II
Author

Thanks Glenn - this is just what I was after...so simple!!!

Contributor II
Author

Thanks Arjun, another great idea...

Contributor II
Author

Hi Christoph,

Not sure i'm doing what you intended. Is this a calculated dimension like Glenns suggestion? I guess not, so I tried using your formula in the sum(sales) measure of the scatter plot but am getting an error on the syntax

put the following formula in the scatter plot measure: sum(\${<Sector = {'*'}>}Measure)

Bad field name: \${<Sect

then tried

sum(\${<Sector = {'*'}>}Sales)

still no joy

guess i'm doing something basic wrong?