Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in NYC Sept 4th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
sagarrahul
Creator
Creator

count diff value in single column

hiiii

need a help

Black List Code
H26.9
H26.9
H26.9
H26.9
H26.9
H25.9
H11.0
H26.9
H26.9
H26.9

in this column i have total 10 values

but in this there are different values

H26.9

H25.9

H11.0

now what i want is to count this individuals in different column and divide by 10

eg :

H26.9 =8

H25.9=1

H11.0=1


so anyone can help me in this


12 Replies
sagarrahul
Creator
Creator
Author

these are my expression in above table :

1)claim_Type

only({<CLM_TYPE={'Reimbursment'}>}CLM_TYPE)

2)total count of claims

=count(Distinct if(len(KeepChar(CLM_XGEN_CLAIM_NUM,'-'))<>3,CLM_XGEN_CLAIM_NUM))

3)ICD Code Blacklist

=Count({<ICD_CODE_LEVEL1_org = { 'Z51.1','Y84.2','Y84.1','Y84.0','Y83.9','Z40.9','Z40.8','Z40.0','S83.6','S83.5','S83.4','S83.3','S83.2','S83.1','S83.0','D06.0','D05.9','D05.7','D24','D27',

'D10.1','D10.0','D09.9','N22.8'} >}

ICD_CODE_LEVEL1_org)

4)Black List Code

=if(WildMatch(ICD_CODE_LEVEL1_org ,'T02.9','T02.8','T02.7','T02.6','T02.5','T02.4','T02.3','S43.3','S43.2','S43.1','S43.0','S42.9','S42.8','S42.7','S42.4','S42.3','S42.2',

'S42.1','S42.0','S62.8','S62.7','S62.6','S62.5','S62.4','S62.3','S62.2','S62.1','S62.0','S22.8','S22.5','S55.9','S55.8','S55.7','S55.1','S55.0','S32.7','S32.5','S32.4','M86.6','M86.5','M86.4','M86.3','M86.2','M86.1','M86.0','M85.6','M85.5','M85.4',

'M22.2','M22.1','M22.0','M21.6','M21.5','M21.4','M21.3','M21.2','M21.1','M21.0'),ICD_CODE_LEVEL1_org)

5)value

=[ICD Code Blacklist] / [total count of claims]


6)status

=If(([Value]) >= 0.7, 'Outlier', 'Not Outlier')

7)Max date

=max(CLM_INTIMATION_DATE)

8)claim no

=MaxString(aggr(MaxString(CLM_REFERENCE_NUM),HOSPITAL_CODE,HOSPITAL_NAME,CLM_XGEN_CLAIM_NUM,CLM_INTIMATION_DATE,ICD_CODE_LEVEL1_org))

9)Current date

=date(Today())


10)Differnce

=[Current date ]-[max_date]+1

11)Claim_num

=if((Differnce =1 ),CLM_REFERENCE_NUM)

now can u help me in this .

tamilarasu
Champion
Champion

Create a straight table and use below expression as calculated dimension

=if(WildMatch(ICD_CODE_LEVEL1_org ,'T02.9','T02.8','T02.7','T02.6','T02.5','T02.4','T02.3','S43.3','S43.2','S43.1','S43.0','S42.9','S42.8','S42.7','S42.4','S42.3','S42.2',

'S42.1','S42.0','S62.8','S62.7','S62.6','S62.5','S62.4','S62.3','S62.2','S62.1','S62.0','S22.8','S22.5','S55.9','S55.8','S55.7','S55.1','S55.0','S32.7','S32.5','S32.4','M86.6','M86.5','M86.4','M86.3','M86.2','M86.1','M86.0','M85.6','M85.5','M85.4',

'M22.2','M22.1','M22.0','M21.6','M21.5','M21.4','M21.3','M21.2','M21.1','M21.0'),ICD_CODE_LEVEL1_org)

Go to expression tab and add expression as

Count(ICD_CODE_LEVEL1_org)

jonathandienst
Partner - Champion III
Partner - Champion III

That should do the trick

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein