topic How do I use Countifs in QLIK sense in App Development

How do I use Countifs in QLIK sense

VickyS — Fri, 28 Oct 2022 04:24:30 GMT

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.

Re: How do I use Countifs in QLIK sense

vinieme12 — Fri, 28 Oct 2022 06:03:47 GMT

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

Re: How do I use Countifs in QLIK sense

BrunPierre — Fri, 28 Oct 2022 06:22:11 GMT

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?

Re: How do I use Countifs in QLIK sense

VickyS — Fri, 28 Oct 2022 07:03:58 GMT

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

Re: How do I use Countifs in QLIK sense

BrunPierre — Fri, 28 Oct 2022 07:20:00 GMT

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

Re: How do I use Countifs in QLIK sense

VickyS — Sun, 30 Oct 2022 13:37:24 GMT

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