Set Analysis for Multiple Selections

[brandon_d]

I am just starting to get my feet wet with Qlik Sense and I am not a coder by trade, so please bear with me. My current table shows more columns than I want it to (see attachment for an example). The purpose of the table is to show surgical case counts by doctor for the past three years and by specific facilities from a field titled Facility Group Name. The formula used to calculate the surgical volume is shown below. The attachment shows the current facility group names and I would like to be able to have the chart only show OSF, RMH, and SAH without having to filter down on them. Thank you in advance!

Sum({$<[Service Year]={$(=Max([Service Year])), $(=Max([Service Year])-1), $(=Max([Service Year])-2), $(=Max([Service Year])-3)}>}Aggr(If(Sum({$<RenFlag = {1}, SurgeryFlag = {1}, SurgAssistFlag = {1}, [Service Year]={$(=Max([Service Year])), $(=Max([Service Year])-1), $(=Max([Service Year])-2), $(=Max([Service Year])-3)}>} Units)>0, 1, 0), Key|Surgery, [Rendering Doctor Name], Key|Date))

[sunny_talwar]

Is OSF, RMH, and SAH part of a dimension? May be try this

Sum({$<[Service Year]={$(=Max([Service Year])), $(=Max([Service Year])-1), $(=Max([Service Year])-2), $(=Max([Service Year])-3)}, DimensionName = {'OSF', 'RMH', 'SAH'}>}Aggr(If(Sum({$<RenFlag = {1}, SurgeryFlag = {1}, SurgAssistFlag = {1}, [Service Year]={$(=Max([Service Year])), $(=Max([Service Year])-1), $(=Max([Service Year])-2), $(=Max([Service Year])-3)}>} Units)>0, 1, 0), Key|Surgery, [Rendering Doctor Name], Key|Date))

[brandon_d]

I do not know all the proper terminology, but looking further into the table it appears that the dimension is called Facility Group Name and these are all locations within that dimension. Using the formula that you provided did not work. Any other suggestions? Thanks!

[sunny_talwar]

Would you be able to share a sample to show your issue?

[brandon_d]

Please refer back to the image in the original post. I would like the Facility Group Dimension that currently contains AGSH, ARSC, CENT, OSF, RMH, SAH, and SHER to only show OSF, RMH, and SAH when I pull it in as a column within my table.

[sunny_talwar]

I don't see a lot of things in the image... I won't know what you have and what you trying to do unless I see it

[Anonymous · Accepted solution]

The attachment shows the current facility group names and I would like to be able to have the chart only show OSF, RMH, and SAH without having to filter down on them. Thank you in advance!

Hi,

Is this a pivot Chart? To get the above try, instead of adding the dimension "Facility Group Name " add a calculated dimension of the below in its place:

=If(Match([Facility Group Name], 'OSF', 'RMH', 'SAH'),[Facility Group Name])

You will also then need to suppress any null values [Facility Group Name]