Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
yankeybeans
Contributor II
Contributor II

Make Histogram from Pivot Table

 Can I make a histogram based on a Pivot Table?

My main table contains Employee_Name, Year, Date, Hours_Worked. It was created by linking multiple tables in the data load editor.

One row contains, for example: John Smith, 2020, 3/3/2020, 8 hours. So there are multiple rows for each Year.

I want a histogram of total hours worked in a year for each employee. It would show, for example, that 100 employees worked over 2,000 hours in 2020.

I could create a pivot table giving total hours worked for each employee. If I filtered this pivot table for a single year, a histogram of the total_hours_worked for the pivot table would give me what I need.

 

Labels (2)
1 Solution

Accepted Solutions
vinieme12
Champion III
Champion III

As below, using a calculated dimension

 

Dimension:

=AGGR( if(sum(hours)>2000,'>2000'

,if(sum(hours)>1000,'1000-2000'

,if(sum(hours)>500,'500-1000' , '0-500' ))) , employee_name)

Measure

=count(employee_name)

 

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

View solution in original post

6 Replies
justISO
Specialist
Specialist

Hi, why you need only histogram? I would use other chart types, like simple combo chart, as measures here are pretty simple: count(distinct Employee_Name), sum(Hours_Worked) and dimension Year (and/or Date as alternative).

vinieme12
Champion III
Champion III

Histograms are for frequency , by your explanation you need sum of hours, not frequency

Do this

Add  a BarChart with 

Dimension = Year

Measure = sum(Hours) 

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
yankeybeans
Contributor II
Contributor II
Author

I guess I wasn't clear. I don't want sum_hours for a given employee.

I do want frequency (histogram) --  frequency of employees who have worked a bined number of hours. For example: 100-employees have worked 0-500 hrs in 2020; 300-employees have worked 500-1,000 hours in 2020; etc.

 

yankeybeans
Contributor II
Contributor II
Author

I guess I wasn't clear. I don't want sum_hours for a given employee.

I do want frequency (histogram) --  frequency of employees who have worked a bined number of hours. For example: 100-employees have worked 0-500 hrs in 2020; 300-employees have worked 500-1,000 hours in 2020; etc.

 

vinieme12
Champion III
Champion III

As below, using a calculated dimension

 

Dimension:

=AGGR( if(sum(hours)>2000,'>2000'

,if(sum(hours)>1000,'1000-2000'

,if(sum(hours)>500,'500-1000' , '0-500' ))) , employee_name)

Measure

=count(employee_name)

 

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
yankeybeans
Contributor II
Contributor II
Author

Worked great. Just used your AGGR dimension in a bar chart with a single y-axis