Count occurrences based on value in separate field

[nlaughton]

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. 

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

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/Year	Number of ID's with greater than count of 2 sales > =50
Mar-19	3
Apr-19	1

[Vegar · Accepted solution]

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:

[nlaughton]

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]

Was it someting like this you where expecting?

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]

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]

You could try this expression:

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