Skip to main content
Announcements
Customer Spotlight: Discover what’s possible with embedded analytics Oct. 16 at 10:00 AM ET: REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to get a count of field2 for each field1 basedo

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

 

MonthStateCalls
JanAK25MonthCount of States with >25 calls
JanAL27Jan2
JanIL15Feb2
FebAK14March5
FebAL50April4
FebMS200
FebTN10
MarchNJ9
MarchNY150
MarchTX25
MarchTN24
MarchNM33
MarchUT90
MarchSD56
AprilNJ45
AprilNY55
AprilTX83
AprilTN31
AprilNM12
1 Solution

Accepted Solutions
daniel_kusiak
Creator II
Creator II

Hi, please check the attached file.

View solution in original post

13 Replies
Anil_Babu_Samineni

Take Straight Table

Dim as Month

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

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
devarasu07
Master II
Master II

Hi,

In your expression,

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

Hope this helps

Deva

daniel_kusiak
Creator II
Creator II

Hi, please check the attached file.

Anonymous
Not applicable
Author

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
Not applicable
Author

Use this in

Anonymous
Not applicable
Author

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;

Not applicable
Author

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

Not applicable
Author

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.

 

MonthStateZoneCalls
JanAK110
JanAK210
JanAK35
JanAL19
JanAL29
JanAL39
JanIL315
FebAK15
FebAK25
FebAK35
FebAL250
FebMS3200
FebTN210
Not applicable
Author

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.

MonthStateZoneCalls
JanAK110
JanAK210
JanAK35
JanAL19
JanAL29
JanAL39
JanIL315
FebAK15
FebAK25
FebAK35
FebAL250
FebMS3200
FebTN210