Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
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
Partner
Partner

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

 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

5 Replies
Vegar
Partner
Partner

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

 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

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
Partner
Partner

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)

 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
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
Partner
Partner

You could try this expression:

 

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

Plees ekskuse my Swenglish and or Norweglish spelling misstakes