Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
wgonzalez
Partner - Creator
Partner - Creator

How to summarize data in a report?

Hi, I need to summarize hours devoted in several categories grouped by Business Name.  Have made several tries, but haven't been able to do it.  The data model is ready, but I'm not sure if it's the best approach.  See attached.

1 Solution

Accepted Solutions
Jason_Michaelides
Luminary Alumni
Luminary Alumni

Yes - your employees and the MasterCalendar are linked via the AltTrans table.  Therefore, any date selection you make will reduce the AltTrans table to the associated DTPunchDates and the Employees table will also be reduced to the records associated to the now reduced Alttrans table.

If you always want the full headcount regardless of any selections, instead of Sum(EmpRecordCtr), use Sum({1} EmpRecordCtr).

Hope this helps,

Jason

PS - Very good practice to use Sum(Ctr) instead of Count(DISTINCT ID) - nice one.

View solution in original post

4 Replies
Jason_Michaelides
Luminary Alumni
Luminary Alumni

Two options:

1: Use a pivot table, set AttCategory as the second dimension and drag it to the top of the chart. Expression = Sum(AttHours)

2: Chart just as you have it. Expression 1 = Sum({<AttCategory={'Category 1'}>} AttHours)

Expression 2 = Sum({<AttCategory={'Category 2'}>} AttHours)

Hope this helps,

Jason

wgonzalez
Partner - Creator
Partner - Creator
Author

I used the second option.  Thanks a lot!

wgonzalez
Partner - Creator
Partner - Creator
Author

Hi Jason,

The report sample is expected to show all Employees in the table.  Each time a date or month is selected (in a multibox) the head count changes.  I'm wondering the type of relationship QV is doing through the association made among the tables Employees and AttTrans.  Could you please clarify?

Thanks.

Jason_Michaelides
Luminary Alumni
Luminary Alumni

Yes - your employees and the MasterCalendar are linked via the AltTrans table.  Therefore, any date selection you make will reduce the AltTrans table to the associated DTPunchDates and the Employees table will also be reduced to the records associated to the now reduced Alttrans table.

If you always want the full headcount regardless of any selections, instead of Sum(EmpRecordCtr), use Sum({1} EmpRecordCtr).

Hope this helps,

Jason

PS - Very good practice to use Sum(Ctr) instead of Count(DISTINCT ID) - nice one.