Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
theglenndavid
Partner - Contributor III
Partner - Contributor III

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

=Aggr(MaxString({<Resource={"=Count(Sector)=1"} >} 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.

View solution in original post

8 Replies
OmarBenSalem

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)

theglenndavid
Partner - Contributor III
Partner - Contributor III

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

=Aggr(MaxString({<Resource={"=Count(Sector)=1"} >} 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.

_armoco_
Partner - Creator II
Partner - Creator II

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

igdrazil
Creator III
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

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

Thanks Arjun, another great idea...

Anonymous
Not applicable
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?