Multiple filter or using multiple conditions

[#1]

Hello Experts

I have done lot of research in the QlikView community to find the solution but didn't find correct solution....Can somebody help me on this....this is little urgent need to find a logic.

I have a list of UserName (Field) aligned to ID(Field) but the UserNames are duplicated and ID are Unique say for example:

UserName      ID

ABC                1

DBC                2

ARP                3

ABC                4

DBC                5

ARP                6

But I need to find a count or getcount of Distinct users

Example: 1 - If ABC is considered in ID (1) then he should not be considered in ID (4)

Example: 2 - If DBC is considered in ID (2) then he should not be considered in ID (5)

(1) I tried with this formula        =Count(UserName = {<ID={'1'}>})      But it's now working

(2) =Count(Distinct UserName = {<ID={'2'}>} =-  {{<ID={'5'}>}})

          Even this didn't work.

(3) Count([UserName] OnlyIf {<ID={'2'},>})   This also didnt work

Thanks a lot in advance

[rahulpawarb]

Hello Vinay,

Please try below:

=Count({<ID={1}>} DISTINCT UserName)

Please let me know if you still won't get desired result.

Regards!

Rahul

[Anil_Babu_Samineni]

Vinay Chandra Nagaraju wrote:

But I need to find a count or getcount of Distinct users

Example: 1 - If ABC is considered in ID (1) then he should not be considered in ID (4)

Example: 2 - If DBC is considered in ID (2) then he should not be considered in ID (5)

That is the case, You just use Min(ID) or FirstSortedValue(ID, Aggr(Sum(ID), UserName))

[awhitfield]

=Count( DISTINCT UserName)

But I am not sure what you are actually trying to achieve??

Andy

[qlikview979]

Hi

Not sure if u r expecting output like

ID,UserName, Count(UserName)

1         ,ABC,                     1

2         ,DBC,                     1

3         ,ARP,                      1

6         ,ARP,                      1

UserName:-if(ID<>4 and ID<>5,UserName)

COunt(UserName):-Count(if(ID<>4 and ID<>5,UserName))

[#6]

Hi Rahul Pawar,

Many thanks for your response in the first place however, the formula you provided me was helpful but it was considering the UserName also which was present in the other ID's.

Basically I want to do multiple filter. 1st time I want Distinct count of UserName for respective ID this I was able to achieve but the Major problem is If "X" UserName is considered in ID number "1" then the next preceding ID number "2 or 3 or 4" should not pick/consider the "X" UserName .

Hope I am clear with this if not please let me know I will try to provide more examples.

Just FYI... I have attached the sample data kindly have a look and let me know your thoughts

This urgent please help.

Thank You,

Vinay

[#7]

Hi Anil,

Many thanks for your response in the first place however, the formula you provided me was helpful but it was considering the UserName also which was present in the other ID's.

Basically I want to do multiple filter. 1st time I want Distinct count of UserName for respective ID this I was able to achieve but the Major problem is If "X" UserName is considered in ID number "1" then the next preceding ID number "2 or 3 or 4" should not pick/consider the "X" UserName .

Hope I am clear with this if not please let me know I will try to provide more examples.

Just FYI... I have attached the sample data kindly have a look and let me know your thoughts

This urgent please help.

Thank You,

Vinay

[#8]

Hi Andrew,

Many thanks for your response in the first place however, the formula you provided me was helpful but it was considering the UserName also which was present in the other ID's.

Basically I want to do multiple filter. 1st time I want Distinct count of UserName for respective ID this I was able to achieve but the Major problem is If "X" UserName is considered in ID number "1" then the next preceding ID number "2 or 3 or 4" should not pick/consider the "X" UserName .

Hope I am clear with this if not please let me know I will try to provide more examples.

Just FYI... I have attached the sample data kindly have a look and let me know your thoughts

This urgent please help.

Thank You,

Vinay

[#9]

Hi Mahesh,

Many thanks for your response in the first place however, the formula you provided me was helpful but it was considering the UserName also which was present in the other ID's.

Basically I want to do multiple filter. 1st time I want Distinct count of UserName for respective ID this I was able to achieve but the Major problem is If "X" UserName is considered in ID number "1" then the next preceding ID number "2 or 3 or 4" should not pick/consider the "X" UserName .

Hope I am clear with this if not please let me know I will try to provide more examples.

Just FYI... I have attached the sample data kindly have a look and let me know your thoughts

This urgent please help.

Thank You,

Vinay

[Anil_Babu_Samineni]

If possible, Can you make excel formula for desired result. So that we may try in QV