topic Re: Count pivot table values based on different criteria in QlikView

Count pivot table values based on different criteria

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I'm trying to do a count on the pivot table values based on different criteria.

Given a pivot table which I have generated from my datasets:

Company	KPI	Target	AAA	BBB	CCC
A1	Response Time	85%	NA	97%	100%
	UpTime	97%	NA	97%	100%
	Service Time 1	85%	NA	100%	100%
B1	UpTime	94%	92%	100%	100%
B1	Service Time 2	85%	100%	100%	100%

I would like to achieve the following table based on the results shown above:

Summary	Subsidiary	AAA	BBB	CCC
Number of achieved KPI		1	4	5
Number of achieved company		0	1	2

I have tried the following expressions:

Option 1: Set Analysis

IF(Summary = 'Number of achieved KPI',

COUNT({<B1_UP = {">=0.94"}, B1_Serv = {">=0.85"}, A1_Resp = {">=0.85"}, A1_UP ={">=0.97"}, A1_Serv = {">=0.85"}>} DISTINCT Target),

IF(Summary = 'Number of achieved accounts',
COUNT({<Target= {"*"}, B1_UP = {"1"}, B1_Serv = {"1"}, A1_Resp = {"1"}, A1_UP ={"1"}, A1_Serv = {"1"}>} DISTINCT Company) ))

Option 2: If-Else statements

IF(Summary = 'Number of achieved KPI',

SUM(AGGR(COUNT(

Target='Response Time' AND A1_Resp >= '0.85'

OR Target='UpTime' AND A1_UP >= '0.97'

OR Target='Service Time 1' AND A1_Serv >= '0.85'

OR Target='UpTime' AND B1_UP >= '0.94'

OR Target='Service Time 2' AND B1_Serv >= '0.85' ),Subsidiary)),

IF(Summary = 'Number of achieved accounts',

SUM(AGGR(COUNT(

(Company = 'A1' AND A1_Resp = '1' AND A1_UP = '1' AND A1_Serv = '1')

OR (Company = 'B2' AND B2_UP = '1' AND B2_Serv = '1')), Subsidiary) ))

Note: B1_UP, A1_Resp etc. are my variables which calculate the percentage values as shown in the first table.

Both options does not give me the correct summary values. Please suggest if you have a better solution. Thanks in advance!

Re: Count pivot table values based on different criteria

sunny_talwar — Tue, 16 Aug 2016 09:36:18 GMT

Would you be able to provide a sample qvw file with your expected output to help you better here?

Re: Count pivot table values based on different criteria

Anonymous — Tue, 16 Aug 2016 10:43:10 GMT

I have come up with some dummy data. Please see attachment.

Re: Count pivot table values based on different criteria

sunny_talwar — Tue, 16 Aug 2016 12:36:50 GMT

What is the output table going to show based on the inputs you have provided? I am still playing around with it and I feel it would be helpful to know what the required output needs to be

Re: Count pivot table values based on different criteria

Anonymous — Wed, 17 Aug 2016 01:30:05 GMT

I would like to achieve the summary table which calculates the "Number of achieved KPI" and "Number of achieved company" for each subsidiary. The results are shown in the 2nd table of my original post.

Number of achieved KPI: If reached target, the KPI is met, count as 1.

Number of achieved company: If all KPIs of a particular company are met, count as 1.

Re: Count pivot table values based on different criteria

Anonymous — Fri, 02 Sep 2016 07:52:30 GMT

Hi Sunny,

I have managed to resolve partially based on the solution given in the link below.

Set Analysis, Multiple Conditions | Qlik Community

However, my CCC's value is only correct upon filtering by Subsidiary.

Do advise if you have any idea why the value does not show.

Thanks.

Re: Count pivot table values based on different criteria

Anonymous — Mon, 28 Aug 2017 09:18:46 GMT

Just an update if anyone is facing the same issue...

I have managed to resolved it by creating variables for individual subsidiaries and create expression using the variables.