Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
mskusace
Contributor

Multiple Column/Dimension Data Visualization

I have a table that has multiple columns that I would like to visualize. Each row of data consists of a user, their associated department, division, and section, which is the drill down I use in the other areas of the dashboard. There are 13 columns that have "Yes" or "No" as the field values. Each of these 13 columns indicates whether or not that user needs certain training in one or more of these 13 areas. I would like to create a bar chart that has each of these 13 columns on the X-axis and the "Yes" count of each respective column on the Y-axis. I can use a Pivot Table and put the "Department" as the row and then each of the 13 columns as a column, to get a total count of "Yes" values. I then manually created a 2 column table with each of the 13 columns in one column and the associated count in the other column. 

Is there a way I can manipulate my data within the bar graph chart to create a "Yes" count of each of the 13 columns?

Raw Data

UserDepartmentDivisionSectionTraining1Training2Training3Training4Training5Training6Training7Training8Training9Training10
User1Department1Division1Section1YesNoNoNoNoNoNoNoNoYes
User2Department1Division2Section2NoNoNoNoNoNoNoYesNoNo
User3Department2Division3Section3YesYesYesYesYesYesYesYesYesYes
User4Department2Division3Section3NoNoNoNoNoNoNoNoNoNo
User5Department3Division4Section4YesNoNoYesNoNoNoYesNoNo

 

Desired Results in Tabular Form

Sum of Training1Sum of Training2Sum of Training3Sum of Training4Sum of Training5Sum of Training6Sum of Training7Sum of Training8Sum of Training9Sum of Training10
3112111312

 

Then I want to create a bar graph with "Training1", "Training2"..."Training10" on the X-axis and the count of each respective training on the Y-axis.

 

Please let me know if I need to clarify further. Any assistance would be greatly appreciated!

1 Solution

Accepted Solutions
Partner
Partner

Re: Multiple Column/Dimension Data Visualization

I used this script.

YESMAP:
Mapping LOAD Bool, dual(Bool,Num)  inline [
Bool, Num
Yes, 1
No, 0];

MAP  [Training done] using 'YESMAP';
Crosstable ('Training type', 'Training done', 4)
LOAD * inline [
User,Department,Division,Section,Training1,Training2,Training3,Training4,Training5,Training6,Training7,Training8,Training9,Training10
User1,Department1,Division1,Section1,Yes,No,No,No,No,No,No,No,No,Yes
User2,Department1,Division2,Section2,No,No,No,No,No,No,No,Yes,No,No
User3,Department2,Division3,Section3,Yes,Yes,Yes,Yes,Yes,Yes,Yes,Yes,Yes,Yes
User4,Department2,Division3,Section3,No,No,No,No,No,No,No,No,No,No
User5,Department3,Division4,Section4,Yes,No,No,Yes,No,No,No,Yes,No,No
];

exit script

 

Then I could reate this app (sceenshot and attached app) Using the training type dimension and sum[(Training done]) as a measureimage.png

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

3 Replies
Partner
Partner

Re: Multiple Column/Dimension Data Visualization

You need to load this table using CrossTable:

After, you can use the training dimension with Count({<Value= {Yes}>}Value)

 

To help users find verified answers, please don't forget to mark a correct resolution or answer to your problem or question as correct.
Partner
Partner

Re: Multiple Column/Dimension Data Visualization

I used this script.

YESMAP:
Mapping LOAD Bool, dual(Bool,Num)  inline [
Bool, Num
Yes, 1
No, 0];

MAP  [Training done] using 'YESMAP';
Crosstable ('Training type', 'Training done', 4)
LOAD * inline [
User,Department,Division,Section,Training1,Training2,Training3,Training4,Training5,Training6,Training7,Training8,Training9,Training10
User1,Department1,Division1,Section1,Yes,No,No,No,No,No,No,No,No,Yes
User2,Department1,Division2,Section2,No,No,No,No,No,No,No,Yes,No,No
User3,Department2,Division3,Section3,Yes,Yes,Yes,Yes,Yes,Yes,Yes,Yes,Yes,Yes
User4,Department2,Division3,Section3,No,No,No,No,No,No,No,No,No,No
User5,Department3,Division4,Section4,Yes,No,No,Yes,No,No,No,Yes,No,No
];

exit script

 

Then I could reate this app (sceenshot and attached app) Using the training type dimension and sum[(Training done]) as a measureimage.png

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

mskusace
Contributor

Re: Multiple Column/Dimension Data Visualization

I apologize for the delayed response. I was trying to play around with the command initially and got stuck. I finally revisited it and I was able to get it work perfectly! I am not sure how you got your columns to be the training type and a single row to be the measure, but I got it to work as the training types in one column and the count in the adjacent column. It really was as simple as using:

Crosstable ('Training type', 'Training done')
LOAD
[Training1],
[Training2],
[Training3],
[Training4],
[Training5],
[Training6],
[Training7],
[Training8],
[Training9],
[Training10]
FROM [Sheet1.xlsx]
(ooxml, embedded labels, table is Sheet1);

I will now try to incorporate the department, division, and section as you have in your example. This will do per my requirements for now!