How to get a count of field2 for each field1 basedo

[#1]

Hello users, I am new to the community and QV usage.  Struggling how to accomplish it in Qlikview Here is how my data is and the result I want to produce.  Appreciate the help.  I tried the count if distinct option, but it is not working for some reason.  As you can see from the data (left three columns), I want to product the results (right 2 columns).  In my result I do not need to show the states, but just the count of states for each month, where the calls are over 25.

Thank you.

Data:                                                       Result

Month	State	Calls
Jan	AK	25		Month	Count of States with >25 calls
Jan	AL	27		Jan	2
Jan	IL	15		Feb	2
Feb	AK	14		March	5
Feb	AL	50		April	4
Feb	MS	200
Feb	TN	10
March	NJ	9
March	NY	150
March	TX	25
March	TN	24
March	NM	33
March	UT	90
March	SD	56
April	NJ	45
April	NY	55
April	TX	83
April	TN	31
April	NM	12

[Anil_Babu_Samineni]

Take Straight Table

Dim as Month

Expression would be -- Count({<Calls = {'>25'}>}State)

[devarasu07]

Hi,

In your expression,

Count({<Calls = {'>=25'}>}State)

Hope this helps

Deva

[daniel_kusiak · Accepted solution]

Hi, please check the attached file.

[Anonymous]

Is there a way , where we can get the desired data directly from scripting instead of showing up in front end.

I meant can we see the desired output in Table viewer . with the help of scripting...

[Anonymous]

Use this in

[Anonymous]

T1:

LOAD
     Month,
     State,
     Calls

    
FROM

(ooxml, embedded labels, table is Sheet1)
Where Calls>=25;

T2:

LOAD
     Month,
     Count(State)
     Resident T1 Group by Month;
    
    
Drop Table T1;

[#8]

Dan, Thank you. I do not see the attachment.

[#9]

Thank you for your responses, the expression is working.  I just didn't realize my data has one more segment within State.  Here is how it looks:

1) So the calls total has to be first aggregated to State level from all Zones, then the state sum has to be checked against 25. 

2) The result should still display all 12 months, even if there are months that do not have any states with > 25, it should still show that month, with a blank for state count

Thank you all.

Month	State	Zone	Calls
Jan	AK	1	10
Jan	AK	2	10
Jan	AK	3	5
Jan	AL	1	9
Jan	AL	2	9
Jan	AL	3	9
Jan	IL	3	15
Feb	AK	1	5
Feb	AK	2	5
Feb	AK	3	5
Feb	AL	2	50
Feb	MS	3	200
Feb	TN	2	10

[#10]

Thank you for your responses, the expression is working.  I just didn't realize my data has one more segment within State.  Here is how it looks:

1) So the calls total has to be first aggregated to State level from all Zones, then the state sum has to be checked against 25.

2) The result should still display all 12 months, even if there are months that do not have any states with > 25, it should still show that month, with a blank for state count

Thank you all.

Month	State	Zone	Calls
Jan	AK	1	10
Jan	AK	2	10
Jan	AK	3	5
Jan	AL	1	9
Jan	AL	2	9
Jan	AL	3	9
Jan	IL	3	15
Feb	AK	1	5
Feb	AK	2	5
Feb	AK	3	5
Feb	AL	2	50
Feb	MS	3	200
Feb	TN	2	10