topic Re: Help to replace Countifs equivalent in Qlik in App Development

Help to replace Countifs equivalent in Qlik

SUPRESH81 — Wed, 06 Jul 2022 15:43:19 GMT

Hello All,

I am looking help, to decode the countifs logic which work in excel and I am looking similar to implement in Qlik , I tried Aggr In chart or group by in SQL however, not able to find how to proceed.

Sample data shared - Flag formula - =IF(COUNTIFS($B$1:$B$19,$B2,$C$1:$C$19,"<"&$C2)>=1,"Y","N")

Data:-

B	C	D	E	F
ID	Date	Desc	Count	Flag
1	01/01/2021	Jan 21 Data	0	N
2	01/02/2022	Feb 22 Data	0	N
3	01/01/2019	Jan 19 Data	0	N
5	01/01/2021	Jan 21 Data	1	Y
7	01/02/2022	Feb 22 Data	0	N
8	01/01/2019	Jan 19 Data	0	N
9	01/01/2021	Jan 21 Data	0	N
9	01/01/2021	Jan 21 Data	0	N
14	01/01/2021	Jan 21 Data	0	N
11	01/01/2021	Jan 21 Data	0	N
12	01/01/2021	Jan 21 Data	0	N
34	01/01/2021	Jan 21 Data	0	N
56	01/01/2021	Jan 21 Data	0	N
89	01/01/2021	Jan 21 Data	0	N
23	01/01/2021	Jan 21 Data	0	N
5	01/01/2021	Jan 21 Data	1	Y
5	01/02/2022	Feb 22 Data	3	Y
5	01/01/2019	Jan 19 Data	0	N

Output :-

Count expected: - Pivot Table

Desc N Y Total

Jan 21 Data	10	2	12
Feb 22 Data	2	1	3
Jan 19 Data	3		3

Re: Help to replace Countifs equivalent in Qlik

Or — Wed, 06 Jul 2022 14:02:48 GMT

Your sample data doesn't seem to match your formula when pasted to Excel, so I'm not quite sure what you're after here. Are you getting the flag from your data source and having issues with the pivot table? All that formula seems to do is check if the number in E is greater than 0, which should be simple to do in Qlik by using if(E > 0,'Y','N') as Flag?

Re: Help to replace Countifs equivalent in Qlik

SUPRESH81 — Wed, 06 Jul 2022 14:34:44 GMT

Hi,

From the source only 3 columns, id, date, desc, Task is to calculate the no of time Id have repeated, however, I need to break it desc wise i.e Month-wise, For eg. Id -5 can see 3 times, 1st - Jan 19, Count 0 so flag N, 2nd - Jan 21, count 1 so flag Y, 3rd time - Feb 22 count 1 and Flag - Y.

In pivot as output showed as breakup - desc wise and Flag as column - to show count it has repeated I,e Flag= Y