Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

wgonzalez
Contributor

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_michaelid
Honored Contributor II

Re: How to summarize data in a report?

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.

4 Replies
jason_michaelid
Honored Contributor II

Re: How to summarize data in a report?

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
Contributor

Re: How to summarize data in a report?

I used the second option.  Thanks a lot!

wgonzalez
Contributor

Re: How to summarize data in a report?

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_michaelid
Honored Contributor II

Re: How to summarize data in a report?

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.

Community Browser