topic Re: how to Count same values one time in QlikView

how to Count same values one time

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

Hi Team,

I have a data like below:

SEAL	TAMS	SOX
1216	T529	Yes
1217	T529	High
1218	T529	Critical
1219	T530	Critical
1220	T531	Pending
1221	T532	Yes
1222	T533	Critical
1223	T533	High
1224	T533	Pending
1225	T533	Medium

Above table "SEAL" data is Unique Values and "TAMS" data is Repeated Values.

My requirement is:

In the above table same TAMS (T529,T533) have multiple SOX values.So i want to calculate tams count based on priority.

SOX priority:

SOX

Yes

Critical

High

Medium

Pending

1)If SOX='Yes' then calculate TAMS count only these TAMS(T529,T532)

2)If SOX='Critical' then calculate TAMS count only these TAMS(T530,T533) .Here T529 ,T533 Id's SOX have Critical But Same Id We are counting in If SOX='Yes'.So no need to calculate again.

Expected Results:

SOX	TAMS COUNT
Yes	2
Critical	2
High	0
Medium	0
Pending	1

Re: how to Count same values one time

sunny_talwar — Wed, 23 Aug 2017 13:40:25 GMT

May be this

=Sum(Aggr(

If(Count(DISTINCT TOTAL <TAMS> {<SOX = {'Yes'}>} TAMS) = 1, Count(DISTINCT {<SOX = {'Yes'}>} TAMS),

If(Count(DISTINCT TOTAL <TAMS> {<SOX = {'Critical'}>} TAMS) = 1, Count(DISTINCT {<SOX = {'Critical'}>} TAMS),

If(Count(DISTINCT TOTAL <TAMS> {<SOX = {'High'}>} TAMS) = 1, Count(DISTINCT {<SOX = {'High'}>} TAMS),

If(Count(DISTINCT TOTAL <TAMS> {<SOX = {'Medium'}>} TAMS) = 1, Count(DISTINCT {<SOX = {'Medium'}>} TAMS),

If(Count(DISTINCT TOTAL <TAMS> {<SOX = {'Pending'}>} TAMS) = 1, Count(DISTINCT {<SOX = {'Pending'}>} TAMS))))))

, TAMS, SOX))

Re: how to Count same values one time

Clever_Anjos — Wed, 23 Aug 2017 13:40:43 GMT

Please use a Straight Table

Dimension: =Aggr(FirstSortedValue(SOX,Match(SOX,'Yes','Critical','High','Medium','Pending')),TAMS)

Expression : Count(Distinct TAMS)