Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have the following data sets (simplified for this post)
Table 1
Resource | Sector |
---|---|
Janet P | Team1 |
David J | Team2 |
Peter N | Team1 |
Sarah P | Team1 |
Michael H | Team2 |
Table 2
Resource | Salary | Sales |
---|---|---|
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
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.
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)
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.
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
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
Thanks Omar for the quick response - this works really well.
Thanks Glenn - this is just what I was after...so simple!!!
Thanks Arjun, another great idea...
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?