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: 
SGood
Contributor III
Contributor III

Calculating the percentage of a total "Subset" of data

I am producing the table below, but can't work out how to calculate the percentage of a subset, but not of the "Total" data.

What I mean is there are three Categories of data, Cat A, Cat B and Cat C.  Cat A contains a number of Task Categories, as shown in the table below, and within each Task Category there are a number of tasks.  I want to show the percentage booked to each Task (per week), but only as a percentage of the Total of Cat A tasks.  I need to exclude any tasks that are Cat B or Cat C completely from the calculation.  This is what  I have so far, but the percentages are calculating based on all the data (including Cat B and Cat C), not just Cat A.  Any help will be greatly appreciated. 

=if(WklyDate>=today()-30 and WklyDate<=today(),
if([RTB Task Category]='Incident Management',
        (num(sum({$<[RTB Task Category]={'Incident Management'}>}[WklyTimebookings])/
        sum({$<[WTProjType]={'Cat A'}>}all [WklyTimebookings]),'#,##0.0%')),
        if([RTB Task Category]='Management',
                (num(sum({$<[RTB Task Category]={'Management'}>}[WklyTimebookings])/
                sum({$<[WTProjType]={'Cat A'}>}all [WklyTimebookings]),'#,##0.0%')),
                if([RTB Task Category]='Operations',
                        (num(sum({$<[RTB Task Category]={'Operations'}>}[WklyTimebookings])/
                        sum({$<[WTProjType]={'Cat A'}>}all [WklyTimebookings]),'#,##0.0%')),
                                (num(sum({$<[RTB Task Category]={''}>}[WklyTimebookings])/
                                sum({$<[WTProjType]={'Cat A'}>}all [WklyTimebookings]),'#,##0.0%')),
))))
 

  Week Commencing 02/05/2022 02/05/2022 09/05/2022 09/05/2022 16/05/2022 16/05/2022
Task Category Task Name Hours Booked % Hours Booked % Hours Booked %
Incident Management Incident management (Critical, High, Medium) 142 0.01% 134 0.01% 146 0.01%
Incident Management Incident management (Low) 663 0.06% 681 0.06% 511 0.04%
Incident Management Problem management - root cause analysis (Critical, High, Medium) 169 0.01% 115 0.01% 130 0.01%
Incident Management Problem management - root cause analysis (Low) 246 0.02% 273 0.02% 150 0.01%
Incident Management Total 1,219 0.10% 1,203 0.10% 937 0.08%
Management Risk and control remediation 213 0.02% 197 0.02% 186 0.02%
Management Service Governance 46 0.00% 62 0.01% 18 0.00%
Management Stakeholder Management 26 0.00% 32 0.00% 20 0.00%
Management Team Management - Performance Management 16 0.00% 27 0.00% 16 0.00%
Management Team Management - RTB Meetings 181 0.02% 231 0.02% 134 0.01%
Management Team Management - Support / Admin 104 0.01% 115 0.01% 82 0.01%
Management Total 586 0.05% 665 0.06% 456 0.04%
Operations Ad-hoc Data Reports 118 0.01% 117 0.01% 97 0.01%
Operations Ad-hoc Service 196 0.02% 159 0.01% 103 0.01%
Operations Capacity/Performance Management 28 0.00% 45 0.00% 33 0.00%
Operations Creation and Review of Service management/ Production MI metrics 99 0.01% 87 0.01% 77 0.01%
Operations Disaster Recovery/Business Continuity Planning - Failover planning and testing 33 0.00% 48 0.00% 18 0.00%
Operations Knowledge management and documentation maintenance 557 0.05% 555 0.05% 452 0.04%
Operations Production Data Updates/loads/patching 210 0.02% 138 0.01% 102 0.01%
Operations Service Monitoring and Responding to Alerts 1,161 0.10% 1,151 0.10% 983 0.08%
Operations Start of Day/End of Day/Production Readiness/Health checks 95 0.01% 106 0.01% 72 0.01%
Operations User communications & support - App/User Training 25 0.00% 23 0.00% 6 0.00%
Operations User communications & support - Password Management 4 0.00% 17 0.00% 16 0.00%
Operations User communications & support - Service Request Management 253 0.02% 267 0.02% 200 0.02%
Operations User communications & support - User Queries 653 0.05% 652 0.05% 711 0.06%
Operations Total 3,430 0.29% 3,365 0.28% 2,869 0.24%
- Asset and configuration management - - 6 0.00% 4 0.00%
- Automation and Optimisation 28 0.00% 26 0.00% 28 0.00%
- Management 8 0.00% 11 0.00% 11 0.00%
- Non Warranty Bug Fix - Deployment 6 0.00% - - - -
- Non Warranty Bug Fix - Development 7 0.00% 8 0.00% - -
- Production Change Scheduling, Release Review, Co-ordination and Air Traffic control 7 0.00% 4 0.00% 9 0.00%
- Regulatory Requirements - - - - 4 0.00%
- Total 56 0.00% 55 0.00% 56 0.00%
Total   5,292 0.00% 5,287 0.00% 4,317 0.00%

 

Labels (1)
1 Reply
rubenmarin

Hi, instead of 'all' try using 'total':

=if(WklyDate>=today()-30 and WklyDate<=today(),
if([RTB Task Category]='Incident Management',
        (num(sum({$<[RTB Task Category]={'Incident Management'}>}[WklyTimebookings])/
        sum({$<[WTProjType]={'Cat A'}>}total[WklyTimebookings]),'#,##0.0%')),

You can also try to set an expresson with just sum({$<[WTProjType]={'Cat A'}>}total[WklyTimebookings]) and check the expression until it returns the value you want, once you have the correct expression for the divisor you can use t in the expression.