How do I use Countifs in QLIK sense

[VickyS]

Hi Everyone,

I am new to Qliksense and struggling to get hands on with its development. I am sure will find solution here.

Qlik sense works unlikely of Excel. I have two issues for which I am seeking help.

Issue: I have Data table with 30 fields with 61,000+ records in rows. due to requirement I did cross table and Table has now become flat with records close to 2,32, 000+. 

Now, I have column with name Status which has around 24 different type of status. I want the Count of one of the Status, However, when I am trying to do so I am getting a count of 59,000 which is incorrect and I think this is due to Flating the Table. The actual count of Type of Status is 14,000 - Example Below.

Application ID	Status
1	Application
2	CTQ
3	Reject
4	Select

I can use Simple Countifs formula in excel and derive the value however, Qlik sense seems to have different approach that I am not aware of.

I can figure out only two ways to achieve this:

1. By counting the value of required Status(eg: Reject) if the count of application is not duplicate in other words distinct count of Application ID column

2. By creating new column and assigning value to duplicate values in Application ID column. Then applying count if using new column.

I every possible scenario I have to use COuntif's. Please suggest. 

[vinieme12]

=count(  {<Status={'Reject'}>}  Distinct ApplicationID)

read more on set analysis here

https://www.analyticsvidhya.com/blog/2014/01/set-analysis-qlikview/

[BrunPierre]

With your approach, perhaps

Count(DISTINCT If(Status='Reject', [Application ID],Null()))

But the use of set analysis is a better method to use over IFs whenever possible, so try this instead.

Count({<Status={'Reject'}>}DISTINCT [Application ID])

More here Count or Count distinct? 

[VickyS]

Thanks a lot! This works perfectly. It was very easy. 

Count({<Status={'Reject'}>}DISTINCT [Application ID])

What if there are multiple conditions like, application ID, and other columns. Just like we do filter in excel to narrow down?

Regards,

Vicky

[BrunPierre · Accepted solution]

Count({<Status={'Reject'},[Application ID]={1,2},Year={2020}>}DISTINCT [Application ID])

More on that here Set analysis

[VickyS]

Thanks a lot Brun, I appreciate your quick assistance. This works perfectly fine.