Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Sara_3
Creator
Creator

how to count data

Hi,

I am creating the sample data table  here :

Code Category Key
112 A 001236
112 A 002589
112 A 002574
456 B 003698
456 B 007896
890 A 002365
890 A 005987
890 A 002365
342 B 001478

.i have 3 fields i want count of "key" of all code where "category" = A. I tried aggr function in expression but did not to get the expected answer. 

my input is Code. for example: when I select code (112 ) it belongs to category A . so my expected answer is count of all key which has category A. 

Expected result:      A =  count key = " 6 "

i have tried this expression but it gives me different answer.

Sum(Aggr(Count(Key),category)

Thanks!

Labels (1)
1 Solution

Accepted Solutions
Saravanan_Desingh

Can u try this?

=Count({1<Category=p()>} Key)

View solution in original post

8 Replies
Saravanan_Desingh

Try this,

commQV28.PNG

=Count({1<Category=p()>} TOTAL Key)
Sara_3
Creator
Creator
Author

Thanks Saravanan , yes your output is correct but it didn't work for me it gave me total count key of all categories. I really appreciated.

Saravanan_Desingh

Can u try this?

=Count({1<Category=p()>} Key)
Sara_3
Creator
Creator
Author

no, it does not work either. Thanks!

MarcoWedel

Can you describe how you tried to implement the proposed solution and how your results are different from your expectation?

Sara_3
Creator
Creator
Author

it worked perfectly with this sample inline table :

=Count({1<Category=p()>} Key)

Test:
Load * inline [
Code,Category,Key
1122, A, 6
1122, A,8
1122, A, 5
456, B, 7
456, B, 1
890, A, 2
890, A, 3
789, B, 4
789, B, 9
678, C, 10
678, C, 12
]
;

Result:

A= 5

B= 4

C=2

i have huge data of 10 categories and hundreds of code , the above expression should work for  it too but dont know why it does not work.

Sara_3
Creator
Creator
Author

Thanks Saravanan , it works actually i didnt put filters in set analysis. now values are correct. I really appreciated, Thanks again one more time.

Sara_3
Creator
Creator
Author

Hello , I need to use above logic with AGGR() function . below is the logic :

below logic when I selected Customer Code (which belongs to finance dept) I get from below logic DATE - DATE / SUM of that code . but I need to get all codes of same dept when I selected Customer Code .

/////////////////////////////////////////////////////////////////////////////////////

ceil((sum(aggr(distinct min({<[Cat Type]= {'102'}, finalCheck ={"1"},Weeks-={"0"}> } [ Date])

-

(if([VFlag] = '1' AND [PN1] = [PN2],

If(PO=[PO 2] and Not Isnull([BN 2]),

aggr(min({<[Cat Type] , finalCheck ={"1"},Weeks -= {"0"}>} [Date]),key)),

if( not isnull (min([Release Date])),
aggr(min({<[Cat Type]= {'102'} , finalCheck ={"1"}, Weeks-= {"0"}>} [Release Date]),key),

[Creation Date]))),key)))/7)

/

Sum(aggr({<finalCheck={"1"}, [Cat Type]= {'102'} >} finalCheck ,key))

////////////////////////////////////////////////////////////////////////////////

the above logic works perfect for when I selected Customer code I will get the sum of days . I need when I selected the Customer code I will get all customer codes which belong to same dept .