data

[sagarrahul]

hiiiii

want a help in this .

this is my table .

CLM_XGEN_CLAIM_NUM           Black List Code

RC-HS15-10334760                              H26.9

RC-HS15-10335711                              H26.9

RC-HS15-10341139                              H26.9

RC-HS15-10343361                              H26.9

RC-HS15-10336529                              H25.9

RC-HS15-10336520                              H26.9

RC-HS15-10337526                              H26.9

  -

RR-HS15-10336940                              R57.2

RR-HS15-10341978 -

  -

RC-HS15-10333637                              N18.4

RR-HS15-10341246                              N18.9

  -

RC-HS15-10331178                              R50.9

RR-HS15-10336696                              N85.2

RC-HS15-10343151                              S52.9

RC-HS15-10333670                              G40.6

RR-HS15-10341978                              G40.6

RR-HS15-10339968                              S52.9

RC-HS15-10330851                              A01.0

RC-HS15-10344631                              S83.5

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

1) in this i want total count of CLM_XGEN_CLAIM_NUM  and  total count individual Black List Code

2) after geting "point 1" it should divide "CLM_XGEN_CLAIM_NUM / total count individual Black List Code"

3) after diving then if the values goes >=0.7 then is should show as "OUTLIER" othervise "NOT OUTLIER".

"help me in Expression "

hope u got what i want .

so please help me in this as soon as possible .

for example :

  CLM_XGEN_CLAIM_NUM                     Black List Code                     value                     status

  RC-HS15-10334760                                         H26.9

  RC-HS15-10335711                                         H26.9

  RC-HS15-10341139                                         H26.9

  RC-HS15-10343361                                         H26.9

  RC-HS15-10336529                                         H25.9

  RC-HS15-10336520                                         H26.9

  RC-HS15-10337526                                         H26.9

total                    7                                                   7                                   1                     OUTLIER

[varshavig12]

Try this:

1.) =count(CLM_XGEN_CLAIM_NUM)

     =count([Black List Code])

2.) =count(CLM_XGEN_CLAIM_NUM)/count([Black List Code])

3.) =if(count(CLM_XGEN_CLAIM_NUM)/count([Black List Code])>=0.7,'OUTLIER',' NOT OUTLIER')

[PrashantSangle]

Hi,

where you want to present those data?? in chart ? or in text object??

Simple count(CLM_XGEN_CLAIM_NUM) return count of CLM_XGEN_CLAIM_NUM

you can use TOTAL key word for advance expression

like

count(TOTAL CLM_XGEN_CLAIM_NUM)

or

If you want group your total w.r.t any dimension then

count(TOTAL<Black List Code> CLM_XGEN_CLAIM_NUM)

Regards,

[Anonymous]

CountTableClaims:

LOAD

    Count(Distinct CLM_XGEN_CLAIM_NUM) as CountClaimsTotal

RESIDENT YourTable;

CountTableCodes:

LOAD

    Count(Distinct [Black List Code]) as CountCodesTotal

RESIDENT YourTable;

LET vTotalCodes = Peek('CountCodesTotal',0,'CountTableCodes');

LET vTotalClaims = Peek('CountClaimsTotal',0,'CountTableClaims');

Final:

LOAD

     $(vTotalClaims)          as TOTAL_CLM_XGEN_CLAIM_NUM,

     $(vTotalCodes)           as TOTAL_BLACK_LIST_CODE,

     $(vTotalClaims)/$(vTotalCodes) as Value,

     IF($(vTotalClaims)/$(vTotalCodes)<0.7 or IsNull($(vTotalClaims)/$(vTotalCodes)),'NOT OUTLIER','OUTLIER') as Status

AUTOGENERATE 1;

Regards!!

[sagarrahul]

but i want individual Black List Code

eg 1 :

RC-HS15-10333637                              N18.4

RR-HS15-10341246                              N18.9

total claim : 2

Black List Code : 2 but different codes

and blacklist code are two but they are different so it should do this   : 

1/2=0.5

here 1 =N18.4 and 2= total count of claim that is 2

therefore 1/2=0.5 so it is not Outlier

eg :2

RC-HS15-10334760                              H26.9

RC-HS15-10335711                              H26.9

RC-HS15-10341139                              H26.9

RC-HS15-10343361                              H26.9

RC-HS15-10336529                              H25.9

RC-HS15-10336520                              H26.9

RC-HS15-10337526                              H26.9

total claim : 7

Black List Code : 7 same codes

7/7=1

therefore 7/7=1 so it is" Outlier"

[sagarrahul]

but i want individual Black List Code

eg 1 :

RC-HS15-10333637                              N18.4

RR-HS15-10341246                              N18.9

total claim : 2

Black List Code : 2 but different codes

and blacklist code are two but they are different so it should do this   : 

1/2=0.5

here 1 =N18.4 and 2= total count of claim that is 2

therefore 1/2=0.5 so it is not Outlier

eg :2

RC-HS15-10334760                              H26.9

RC-HS15-10335711                              H26.9

RC-HS15-10341139                              H26.9

RC-HS15-10343361                              H26.9

RC-HS15-10336529                              H25.9

RC-HS15-10336520                              H26.9

RC-HS15-10337526                              H26.9

total claim : 7

Black List Code : 7 same codes

7/7=1

therefore 7/7=1 so it is" Outlier"

[sagarrahul]

now can any one help me in this please its urgent

[varshavig12]

=aggr(count(CLM_XGEN_CLAIM_NUM),[Black List Code])

=count([Black List Code])

=aggr(count(CLM_XGEN_CLAIM_NUM),[Black List Code])/count([Black List Code])

=if(aggr(count(CLM_XGEN_CLAIM_NUM),[Black List Code])/count([Black List Code])>=0.7,'OUTLIER',' NOT OUTLIER')

[susovan]

Hi Sagar,

Try this attachment,

[sagarrahul]

thank you for ur help help but what want

eg :2

RC-HS15-10334760                              H26.9

RC-HS15-10335711                              H26.9

RC-HS15-10341139                              H26.9

RC-HS15-10343361                              H26.9

RC-HS15-10336529                              H26.9

RC-HS15-10336520                              H26.9

RC-HS15-10337526                              H26.9

RC-HS15-10333637                              N18.4

RR-HS15-10341246                              N18.9

total claim : 9

Black List Code : H26.9 = 7   so 7/9 =0.77 , hence its is outlier as it is >=0.7

                           N18.4 = 1   so 1/9 =0.11 , hence its is not outlier as it is not >=0.7

                            N18.9 =1   so 1/9 =0.11 , hence its is not outlier as it is not >=0.7