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: 
nlaughton
Contributor II
Contributor II

Count occurrences based on value in separate field

hello i am trying create an expression that counts the number of times  an a field has more than 2 occurrences of a specified value of another field. 

 

for example i have data that looks something like this. 

IDMon/YearSales
AMar-1950
AMar-1950
BMar-1950
BMar-1960
CMar-1950
CMar-1950
AApr-1960
AApr-1950
AApr-1950
BApr-1940
BApr-1920
CApr-1920
CApr-1950

 

what i want is an expression that calculates the  number of id's that had more than 2 instances of sales greater than 50 in a Mon/Year. 

 

something like the following. 

Mon/YearNumber of ID's with greater than count of 2 sales > =50
Mar-193
Apr-191
1 Solution

Accepted Solutions
Vegar
MVP
MVP

I used this expression:

 count({< TypeID = {"=count({<Sales = {[>=50]}>} ID)>1"}>} distinct ID)

On top of this script:

Load AutoNumberHash128(ID, [Mon/Year]) as TypeID, * inline[
ID,Mon/Year,Sales
A,Mar-19,50
A,Mar-19,50
B,Mar-19,50
B,Mar-19,60
C,Mar-19,50
C,Mar-19,50
A,Apr-19,60
A,Apr-19,50
A,Apr-19,50
B,Apr-19,40
B,Apr-19,20
C,Apr-19,20
C,Apr-19,50
];

I get this output:

image.png

 

View solution in original post

5 Replies
Vegar
MVP
MVP

I used this expression:

 count({< TypeID = {"=count({<Sales = {[>=50]}>} ID)>1"}>} distinct ID)

On top of this script:

Load AutoNumberHash128(ID, [Mon/Year]) as TypeID, * inline[
ID,Mon/Year,Sales
A,Mar-19,50
A,Mar-19,50
B,Mar-19,50
B,Mar-19,60
C,Mar-19,50
C,Mar-19,50
A,Apr-19,60
A,Apr-19,50
A,Apr-19,50
B,Apr-19,40
B,Apr-19,20
C,Apr-19,20
C,Apr-19,50
];

I get this output:

image.png

 

nlaughton
Contributor II
Contributor II
Author

I'M still struggling with this is there a way to do this with out adding the extra hash field and only doing it through the chart and the expression.  

the real data behind this is complicated and i'd like to not try and change the load script. 

 

as an example see the attached. what i would like to find is the distinct id's that have greater than 60 in sales on more than 4 days in any one month. 

 

 

Vegar
MVP
MVP

Was it someting like this you where expecting?

Vegar_0-1587506685825.png

The expression for ID dimension is as follows:

=aggr(
   if(COUNT({<ID,SALES={[>60]},SALESDATE={[<$(=max(SALESDATE))>=<$(=addmonths(max(SALESDATE),-1))]}>}SALES)>4,
   only({1}ID),
   NULL()
  )
,ID)

 

nlaughton
Contributor II
Contributor II
Author

is there a way i could turn that expression your wrote into a count, so that i would know how many Id's that equals?

Vegar
MVP
MVP

You could try this expression:

 

=count(aggr(only({<ID = {"=COUNT({<ID, SALES={[>60]} ,SALESDATE = {[<$(=max(SALESDATE))>=<$(=addmonths(max(SALESDATE),-1))]}>}SALES)>4"}>}ID),ID))