Count pivot table values based on different criteria

[Anonymous]

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	Subsidiary	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%
	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!

[sunny_talwar]

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

[Anonymous]

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

[sunny_talwar]

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

[Anonymous]

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.

[Anonymous]

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.

[Anonymous]

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.