Count of ID which got 'OS' rating for two consecutive years ...

[shekhar_analyti]

Hi All ,

How to show Count of ID which got 'OS' rating for two consecutive years in a Text Box ?

How to show Count of ID which never got 'OS' rating in a Text Box ?

Thanks

Shekar

[effinty2112]

Hi Shekhar,

This is a bit of fun but it works for the limited case of only three consecutive years being considered in your data.

Try:

=sum(aggr(if(

Odd(sum({$<Rating = {'OS'}>}Year))

and

count({$<Rating = {'OS'}>}Year) >1

,1,0),ID))

It doesn't count anyone with three consecutive 'OS' - that would be easy enough to take care of by nesting this inside another expression.

This takes advantage of the fact the the sum of two consecutive years is an odd number.

cheers

Andrew

[antoniotiman · Accepted solution]

Hi,

try like this in Text Box

=Count(DISTINCT Aggr((If(Above(TOTAL Rating,1,1) = 'OS' and Rating='OS',ID)),First_Name,Year,Rating))

Regards,

Antonio

[shekhar_analyti]

wow... great workaround ... ...

BUT I HAVE REAL TIME DATA WITH 8 YEARS OF RATING . AND WILL KEEP INCREASING

[effinty2112]

Hi Shekhar,

If you can give me data for more years I'd like to test this:

=Sum(

aggr(

if(Index(

concat(

aggr(

Concat(

Aggr(Rating,(Year,(Numeric, Ascending)),ID),'',Year),

ID)),

'OSOS')>0,1),

ID))

Cheers

Andrew

[shekhar_analyti]

Hi Andrew ,

I cant share the data as is , but give me some time to create replica of it .

In the mean time , what should be expression for getting count of records WHO ONLY HAVE 'OS' RATING , in all of their appraisals .

[antoniotiman]

Have You tried my expression ?

[effinty2112]

Hi Shektar,

Try:

=count({$-<ID = P({<Rating-={'OS'}>})>}DISTINCT ID)

[effinty2112]

Hi Antonio,

Your solution is neater than mine.

Cheers

Andrew

[antoniotiman]

Thanks Andrew.

Regards,

Antonio

[tresesco]

To make the solution more versatile, you might want to add the sorting parameter in aggr()?