Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in NYC Sept 4th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Multiple filter or using multiple conditions

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

10 Replies
rahulpawarb
Specialist III
Specialist III

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))

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
awhitfield
Partner - Champion
Partner - Champion

=Count( DISTINCT UserName)

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

Andy

qlikview979
Specialist
Specialist

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))

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful