Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

pokermon
New 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
New Contributor III

Re: Set analysis in a dimension??

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.

8 Replies
OmarBenSalem
Esteemed Contributor

Re: Set analysis in a dimension??

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
New Contributor III

Re: Set analysis in a dimension??

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_
Contributor II

Re: Set analysis in a dimension??

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

Re: Set analysis in a dimension??

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
New Contributor II

Re: Set analysis in a dimension??

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

pokermon
New Contributor II

Re: Set analysis in a dimension??

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

pokermon
New Contributor II

Re: Set analysis in a dimension??

Thanks Arjun, another great idea...

pokermon
New Contributor II

Re: Set analysis in a dimension??

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?

Community Browser