Qlik Community

Ask a Question

New to Qlik Sense

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

Announcements
QlikWorld May 10-12, Online and Free! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
pokermon
Contributor II
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
theglenndavid
Partner
Partner

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
Partner
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)

theglenndavid
Partner
Partner

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

_armoco_
Partner
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

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

pokermon
Contributor II
Contributor II
Author

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

pokermon
Contributor II
Contributor II
Author

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

pokermon
Contributor II
Contributor II
Author

Thanks Arjun, another great idea...

pokermon
Contributor II
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?