Group multiple measures into a dimension

[andnichols]

Hi, 

I am building a dashboard with Raw Data from REDCap and one of the data fields is for the department. Below is how the data is pulled from REDCap. I want to have all of these departments in one dimension as "Department". Is there a way to do that? I originally was creating a master measure for each one like this: Sum({<[Patient Department (choice=2 WEST)]={'Checked'}>}[Number of Encounters]) but I have about 65 departments and didn't love that way of doing it. Thank you for any help!

Patient Department (choice=2 EAST-PICU)	Patient Department (choice=2 WEST)	Patient Department (choice=2B STEPDOWN PACU)	Patient Department (choice=2B TELEMETRY)	Patient Department (choice=3 EAST)	Patient Department (choice=3 WEST)	Patient Department (choice=4 EAST)	Patient Department (choice=4 WEST)	Patient Department (choice=ADOLESCENT MEDICINE)	Patient Department (choice=ALLERGY)
Unchecked	Unchecked	Unchecked	Checked	Unchecked	Unchecked	Unchecked	Unchecked	Unchecked	Unchecked
Unchecked	Unchecked	Unchecked	Unchecked	Unchecked	Unchecked	Unchecked	Unchecked	Unchecked	Unchecked
Unchecked	Unchecked	Unchecked	Unchecked	Unchecked	Unchecked	Checked	Unchecked	Unchecked	Unchecked
Unchecked	Unchecked	Unchecked	Unchecked	Unchecked	Unchecked	Unchecked	Unchecked	Unchecked	Unchecked

[vinieme12 · Accepted solution]

Use the Crosstable load to transpose the columns to row, refer below blog

https://community.qlik.com/t5/Design/The-Crosstable-Load/ba-p/1468083

something like below

Crosstable(1,Labels,Values)

Load 

'DummyDimension' as Dim

,*

From SomeSourcefile;

drop field Dim

[Vegar · Accepted solution]

It seems to be a typo in you crosstable function @vinieme12 . I assume you wanted it to say Crosstable(Labels,Values,1) ?

When you've done a crosstable, lets assume  

Crosstable(Department,CheckedStatus, x)

Then you could use Department as a dimension  and something like this as a measure:

Sum({<[CheckedStatus]={'Checked'}>}[Number of Encounters]

[andnichols]

Thank you, both! The Crosstable function worked perfectly for what I needed. I really appreciate it.