topic Re: Count occurrences based on value in separate field in QlikView

Count occurrences based on value in separate field

nlaughton — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Count occurrences based on value in separate field

Vegar — Sat, 21 Mar 2020 00:01:43 GMT

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:

Re: Count occurrences based on value in separate field

nlaughton — Mon, 20 Apr 2020 19:14:42 GMT

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.

Re: Count occurrences based on value in separate field

Vegar — Tue, 21 Apr 2020 22:07:10 GMT

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)

Re: Count occurrences based on value in separate field

nlaughton — Wed, 22 Apr 2020 14:57:46 GMT

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?

Re: Count occurrences based on value in separate field

Vegar — Wed, 22 Apr 2020 19:41:22 GMT

You could try this expression:

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