topic Re: dividing single value with total in QlikView

dividing single value with total

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

hii good morning need a help

this is my table

HOSPITAL_NAME	HOSPITAL_CODE	CLM_XGEN_CLAIM_NUM	CLM_INTIMATION_DATE	Clm_Type	ICD_CODE_LEVEL1_org	total count of claims	ICD Code Blacklist	Value
ADITYA HOSPITALS	HEGIC-HS-23852	RR-HS15-10335372	01-Feb-2016	REIMBURSMENT	R50.9	1	1	0.1000
ADITYA HOSPITALS	HEGIC-HS-23852	RR-HS15-10335372	01-Feb-2016	REIMBURSMENT	-	1	0	0.1000
ADITYA HOSPITALS	HEGIC-HS-23852	RR-HS15-10336083	05-Feb-2016	REIMBURSMENT	S42.0	1	1	0.1000
ADITYA HOSPITALS	HEGIC-HS-23852	RR-HS15-10336083	05-Feb-2016	REIMBURSMENT	-	1	0	0.1000
ADITYA HOSPITALS	HEGIC-HS-23852	RR-HS15-10338653	22-Feb-2016	REIMBURSMENT	N39.0	1	1	0.1000
ADITYA HOSPITALS	HEGIC-HS-23852	RR-HS15-10338653	22-Feb-2016	REIMBURSMENT	-	1	0	0.1000
ADITYA HOSPITALS	Total					3	3	0.3000

i want to divide ( individual ICD_CODE_LEVEL1_org /total count of claims )

eg :

R50.9/3

means 1/3=0.3333

i am getting a problem its dividing whole column as u can see i have mark it in yellow colour

so can any one help me in this !!!!!!

Re: dividing single value with total

Anonymous — Tue, 21 Jun 2016 06:26:10 GMT

Hi Sagar:

Count(ICD_CODE_LEVEL1_org)/COUNT(TOTAL CLM_XGEN_CLAIM_NUM)

You have 1 claim by ICD_CODE_LEVEL, and 3 claims in total.

Change on chart properties, the number format of your expression.

Regards.

Re: dividing single value with total

sagarrahul — Tue, 21 Jun 2016 06:32:35 GMT

thank you manuel capella

Change on chart properties, the number format of your expression.

can u guide me in this in detail

Re: dividing single value with total

Anonymous — Tue, 21 Jun 2016 06:37:06 GMT

Hi Sagar,

See that picture. On chart properties, search the number tab. Select your Expression and then, select Fixed to, and set all decimals do you wanna see.

Regards!!

Re: dividing single value with total

brijeshvma — Tue, 21 Jun 2016 06:37:33 GMT

Hi Sagar,

if you want to show result in table ... then you can Write

Count(ICD_CODE_LEVEL1_org)/COUNT(TOTAL CLM_XGEN_CLAIM_NUM)

and if you want to divide by value then

Sum(ICD_CODE_LEVEL1_org)/COUNT(TOTAL CLM_XGEN_CLAIM_NUM)

if you share your sample data so able to show you

Re: dividing single value with total

sagarrahul — Tue, 21 Jun 2016 06:52:05 GMT

need one help

this is my table

HOSPITAL_NAME	HOSPITAL_CODE	CLM_XGEN_CLAIM_NUM	CLM_INTIMATION_DATE	Clm_Type	ICD_CODE_LEVEL1_org	total count of claims	ICD Code Blacklist	Value
SHRADDHA HOSPITAL	HEGIC-NN-16813	RR-HS15-10340819	05-Mar-2016	REIMBURSMENT	B54	1	1	0.111
SHRADDHA HOSPITAL	HEGIC-NN-16813	RR-HS15-10340819	05-Mar-2016	REIMBURSMENT	-	1	0	0.000
SHRADDHA HOSPITAL	HEGIC-NN-24044	RR-HS15-10337106	12-Feb-2016	REIMBURSMENT	J33.9	1	1	0.111
SHRADDHA HOSPITAL	HEGIC-NN-24044	RR-HS15-10337106	12-Feb-2016	REIMBURSMENT	-	1	0	0.000
SHRADDHA HOSPITAL	HEGIC-NN-24044	RR-HS16-10351400	12-May-2016	REIMBURSMENT	R50.9	1	1	0.111
SHRADDHA HOSPITAL	HEGIC-NN-24044	RR-HS16-10351400	12-May-2016	REIMBURSMENT	-	1	0	0.000
SHRADDHA HOSPITAL	HEGIC-NN-47415	RR-HS16-10350129	04-May-2016	REIMBURSMENT	A98.8	1	1	0.111
SHRADDHA HOSPITAL	HEGIC-NN-47415	RR-HS16-10350129	04-May-2016	REIMBURSMENT	-	1	0	0.000
SHRADDHA HOSPITAL	HEGIC-NN-58389	RR-HC16-10345428	05-Apr-2016	REIMBURSMENT	A90	1	1	0.111
SHRADDHA HOSPITAL	HEGIC-NN-58389	RR-HC16-10345428	05-Apr-2016	REIMBURSMENT	-	1	0	0.000
SHRADDHA HOSPITAL	HEGIC-NN-58389	RR-HS15-10342814	18-Mar-2016	REIMBURSMENT	A79.9	1	1	0.111
SHRADDHA HOSPITAL	HEGIC-NN-58389	RR-HS15-10342814	18-Mar-2016	REIMBURSMENT	R50.9	1	1	0.111
SHRADDHA HOSPITAL	HEGIC-NN-58389	RR-HS15-10342814	18-Mar-2016	REIMBURSMENT	-	1	0	0.000
SHRADDHA HOSPITAL	HEGIC-NN-58389	RR-HS15-10342827	18-Mar-2016	REIMBURSMENT	R50.9	1	1	0.111
SHRADDHA HOSPITAL	HEGIC-NN-58389	RR-HS15-10342827	18-Mar-2016	REIMBURSMENT	-	1	0	0.000
SHRADDHA HOSPITAL	HEGIC-NN-58389	RR-HS15-10343635	23-Mar-2016	REIMBURSMENT	A91	1	1	0.111
SHRADDHA HOSPITAL	HEGIC-NN-58389	RR-HS15-10343635	23-Mar-2016	REIMBURSMENT	-	1	0	0.000
SHRADDHA HOSPITAL	Total					8	9	0.778

in this i have marked column ICD_CODE_LEVEL1_org in that there are certain data which is repeated so i want

there sum divided by TOTAL CLM_XGEN_CLAIM_NUM

eg :

R50.9 is respeated 3 times

so i want 3/8=0.375

can u help me in this also

Re: dividing single value with total

Anonymous — Tue, 21 Jun 2016 06:54:14 GMT

Hi Sagar:

Count(DISTINCT ICD_CODE_LEVEL1_org)/COUNT(TOTAL DISTINCT CLM_XGEN_CLAIM_NUM)

Regards!!

Re: dividing single value with total

sagarrahul — Tue, 21 Jun 2016 06:59:25 GMT

see i am not getting what i want

HOSPITAL_NAME	HOSPITAL_CODE	CLM_XGEN_CLAIM_NUM	CLM_INTIMATION_DATE	Clm_Type	ICD_CODE_LEVEL1_org	total count of claims	ICD Code Blacklist
ROCKLAND HOSPITAL LTD	HEGIC-HS-14794	RR-HS15-10330924	02-Jan-2016	REIMBURSMENT	R39.8	1	1
ROCKLAND HOSPITAL LTD	HEGIC-HS-14794	RR-HS15-10330924	02-Jan-2016	REIMBURSMENT	T80.9	1	1
ROCKLAND HOSPITAL LTD	HEGIC-HS-14794	RR-HS15-10330924	02-Jan-2016	REIMBURSMENT	-	1	0
ROCKLAND HOSPITAL LTD	HEGIC-HS-14794	RR-HS15-10337460	14-Feb-2016	REIMBURSMENT	J20.9	1	1
ROCKLAND HOSPITAL LTD	HEGIC-HS-14794	RR-HS15-10337460	14-Feb-2016	REIMBURSMENT	-	1	0
ROCKLAND HOSPITAL LTD	HEGIC-HS-14794	RR-HS16-10349433	30-Apr-2016	REIMBURSMENT	I25.4	1	1
ROCKLAND HOSPITAL LTD	HEGIC-HS-14794	RR-HS16-10349433	30-Apr-2016	REIMBURSMENT	-	1	0
ROCKLAND HOSPITAL LTD	HEGIC-HS-14794	RR-HS16-10349435	30-Apr-2016	REIMBURSMENT	I25.4	1	1
ROCKLAND HOSPITAL LTD	HEGIC-HS-14794	RR-HS16-10349435	30-Apr-2016	REIMBURSMENT	-	1	0
ROCKLAND HOSPITAL LTD	Total					4	5

Re: dividing single value with total

sagarrahul — Tue, 21 Jun 2016 07:01:17 GMT

it is not getting full table will divide it

HOSPITAL_NAME	HOSPITAL_CODE	CLM_XGEN_CLAIM_NUM	CLM_INTIMATION_DATE
ROCKLAND HOSPITAL LTD	HEGIC-HS-14794	RR-HS15-10330924	02-Jan-2016
ROCKLAND HOSPITAL LTD	HEGIC-HS-14794	RR-HS15-10330924	02-Jan-2016
ROCKLAND HOSPITAL LTD	HEGIC-HS-14794	RR-HS15-10330924	02-Jan-2016
ROCKLAND HOSPITAL LTD	HEGIC-HS-14794	RR-HS15-10337460	14-Feb-2016
ROCKLAND HOSPITAL LTD	HEGIC-HS-14794	RR-HS15-10337460	14-Feb-2016
ROCKLAND HOSPITAL LTD	HEGIC-HS-14794	RR-HS16-10349433	30-Apr-2016
ROCKLAND HOSPITAL LTD	HEGIC-HS-14794	RR-HS16-10349433	30-Apr-2016
ROCKLAND HOSPITAL LTD	HEGIC-HS-14794	RR-HS16-10349435	30-Apr-2016
ROCKLAND HOSPITAL LTD	HEGIC-HS-14794	RR-HS16-10349435	30-Apr-2016
ROCKLAND HOSPITAL LTD	Total

Clm_Type	ICD_CODE_LEVEL1_org	total count of claims	ICD Code Blacklist
REIMBURSMENT	R39.8	1	1
REIMBURSMENT	T80.9	1	1
REIMBURSMENT	-	1	0
REIMBURSMENT	J20.9	1	1
REIMBURSMENT	-	1	0
REIMBURSMENT	I25.4	1	1
REIMBURSMENT	-	1	0
REIMBURSMENT	I25.4	1	1
REIMBURSMENT	-	1	0
		4	5

now it is clear to u get it sorted

Re: dividing single value with total

Anonymous — Tue, 21 Jun 2016 07:10:01 GMT

Count of ICD_CODE -> =Count(DISTINCT ICD_CODE_LEVEL1_org)

Count of CLAIM_NUM -> Count(DISTINCT CLM_XGEN_CLAIM_NUM)

Division -> Count(DISTINCT ICD_CODE_LEVEL1_org)/Count(DISTINCT CLM_XGEN_CLAIM_NUM)

Regards!

Re: dividing single value with total

sagarrahul — Tue, 21 Jun 2016 07:16:07 GMT

its not working

will give u table

HOSPITAL_NAME	HOSPITAL_CODE	CLM_XGEN_CLAIM_NUM	CLM_INTIMATION_DATE
ALL INDIA INSTITUTE OF MEDICAL SCIENCES	HEGIC-NN-14636	RR-HS15-10342246	15-Mar-2016
ALL INDIA INSTITUTE OF MEDICAL SCIENCES	HEGIC-NN-14636	RR-HS15-10342246	15-Mar-2016
ALL INDIA INSTITUTE OF MEDICAL SCIENCES	HEGIC-NN-14636	RR-HS15-10342248	15-Mar-2016
ALL INDIA INSTITUTE OF MEDICAL SCIENCES	HEGIC-NN-14636	RR-HS15-10342248	15-Mar-2016
ALL INDIA INSTITUTE OF MEDICAL SCIENCES	HEGIC-NN-14636	RR-HS15-10342249	15-Mar-2016
ALL INDIA INSTITUTE OF MEDICAL SCIENCES	HEGIC-NN-14636	RR-HS15-10342249	15-Mar-2016
ALL INDIA INSTITUTE OF MEDICAL SCIENCES	HEGIC-NN-14636	RR-HS16-10346621	12-Apr-2016
ALL INDIA INSTITUTE OF MEDICAL SCIENCES	HEGIC-NN-14636	RR-HS16-10346621	12-Apr-2016
ALL INDIA INSTITUTE OF MEDICAL SCIENCES	HEGIC-NN-14636	RR-HS16-10347804	20-Apr-2016
ALL INDIA INSTITUTE OF MEDICAL SCIENCES	HEGIC-NN-14636	RR-HS16-10347804	20-Apr-2016
ALL INDIA INSTITUTE OF MEDICAL SCIENCES	HEGIC-NN-14636	RR-HS16-10349821	03-May-2016
ALL INDIA INSTITUTE OF MEDICAL SCIENCES	HEGIC-NN-14636	RR-HS16-10349821	03-May-2016
ALL INDIA INSTITUTE OF MEDICAL SCIENCES	HEGIC-NN-14636	RR-HS16-10349825	03-May-2016
ALL INDIA INSTITUTE OF MEDICAL SCIENCES	HEGIC-NN-14636	RR-HS16-10349825	03-May-2016
ALL INDIA INSTITUTE OF MEDICAL SCIENCES	HEGIC-NN-14636	RR-HS16-10349827	03-May-2016
ALL INDIA INSTITUTE OF MEDICAL SCIENCES	HEGIC-NN-14636	RR-HS16-10349827	03-May-2016
ALL INDIA INSTITUTE OF MEDICAL SCIENCES	HEGIC-NN-14636	RR-HS16-10349828	03-May-2016
ALL INDIA INSTITUTE OF MEDICAL SCIENCES	HEGIC-NN-14636	RR-HS16-10349828	03-May-2016
ALL INDIA INSTITUTE OF MEDICAL SCIENCES	Total

Clm_Type	ICD_CODE_LEVEL1_org	total count of claims	ICD Code Blacklist	Value
REIMBURSMENT	C91.0	1	1	0.111
REIMBURSMENT	-	1	0	0.000
REIMBURSMENT	C91.0	1	1	0.111
REIMBURSMENT	-	1	0	0.000
REIMBURSMENT	C91.0	1	1	0.111
REIMBURSMENT	-	1	0	0.000
REIMBURSMENT	N18.5	1	1	0.111
REIMBURSMENT	-	1	0	0.000
REIMBURSMENT	C08.0	1	1	0.111
REIMBURSMENT	-	1	0	0.000
REIMBURSMENT	C91.0	1	1	0.111
REIMBURSMENT	-	1	0	0.000
REIMBURSMENT	C91.0	1	1	0.111
REIMBURSMENT	-	1	0	0.000
REIMBURSMENT	C91.0	1	1	0.111
REIMBURSMENT	-	1	0	0.000
REIMBURSMENT	C91.0	1	1	0.111
REIMBURSMENT	-	1	0	0.000
		9	9	0.333

C91.0 is repeated 7 times so it should go 7/9 and the ans but is divinding 1/9

Re: dividing single value with total

Anonymous — Tue, 21 Jun 2016 07:22:09 GMT

Hi sagar,

You are not counting distinct.

If you want divide by total, write TOTAL on your Expression.

If you wanna count distinct values, write DISTINCT on your expression.

Re: dividing single value with total

sagarrahul — Tue, 21 Jun 2016 09:13:51 GMT

see i my table both are coming

in one hos[ital repeated data is coming but in another its not coming

now how to handle it

this is my table below

HOSPITAL_NAME	HOSPITAL_CODE	CLM_XGEN_CLAIM_NUM	CLM_INTIMATION_DATE
ALL INDIA INSTITUTE OF MEDICAL SCIENCE	HEGIC-NN-57847	RR-HS16-10356311	10-Jun-2016
ALL INDIA INSTITUTE OF MEDICAL SCIENCE	HEGIC-NN-67133	RR-HS16-10354704	01-Jun-2016
ALL INDIA INSTITUTE OF MEDICAL SCIENCE	HEGIC-NN-67133	RR-HS16-10354704	01-Jun-2016
ALL INDIA INSTITUTE OF MEDICAL SCIENCE	HEGIC-NN-67133	RR-HS16-10354707	01-Jun-2016
ALL INDIA INSTITUTE OF MEDICAL SCIENCE	HEGIC-NN-67133	RR-HS16-10354707	01-Jun-2016
ALL INDIA INSTITUTE OF MEDICAL SCIENCE	HEGIC-NN-67133	RR-HS16-10354713	01-Jun-2016
ALL INDIA INSTITUTE OF MEDICAL SCIENCE	HEGIC-NN-67133	RR-HS16-10354713	01-Jun-2016
ALL INDIA INSTITUTE OF MEDICAL SCIENCE	Total
ALL INDIA INSTITUTE OF MEDICAL SCIENCE..	HEGIC-NN-58922	RR-HS15-10331196	05-Jan-2016
ALL INDIA INSTITUTE OF MEDICAL SCIENCE..	HEGIC-NN-58922	RR-HS15-10331196	05-Jan-2016
ALL INDIA INSTITUTE OF MEDICAL SCIENCE..	HEGIC-NN-58922	RR-HS15-10331196	05-Jan-2016
ALL INDIA INSTITUTE OF MEDICAL SCIENCE..	HEGIC-NN-58922	RR-HS16-10349824	03-May-2016
ALL INDIA INSTITUTE OF MEDICAL SCIENCE..	HEGIC-NN-58922	RR-HS16-10349824	03-May-2016
ALL INDIA INSTITUTE OF MEDICAL SCIENCE..	HEGIC-NN-58922	RR-HS16-10356568	13-Jun-2016
ALL INDIA INSTITUTE OF MEDICAL SCIENCE..	Total

Clm_Type	ICD_CODE_LEVEL1_org	total count of claims	ICD Code Blacklist
REIMBURSMENT	-	1	0
REIMBURSMENT	C91.0	1	1
REIMBURSMENT	-	1	0
REIMBURSMENT	C91.0	1	1
REIMBURSMENT	-	1	0
REIMBURSMENT	C91.0	1	1
REIMBURSMENT	-	1	0
		4	3
REIMBURSMENT	K74.6	1	1
REIMBURSMENT	K92.2	1	1
REIMBURSMENT	-	1	0
REIMBURSMENT	C91.0	1	1
REIMBURSMENT	-	1	0
REIMBURSMENT	-	1	0
		3	3

Re: dividing single value with total

Anonymous — Tue, 21 Jun 2016 09:31:03 GMT

Hi sagar,

Count(TOTAL <HOSPITAL_CODE> DISTINCT ICD_CODE_LEVEL1_org) for total distinct by hospital

Regards!

Re: dividing single value with total

sagarrahul — Tue, 21 Jun 2016 09:33:38 GMT

but there are too many hospital and codes given to it

then what to do

Re: dividing single value with total

Anonymous — Tue, 21 Jun 2016 09:40:21 GMT

On Total, you can indicate for which groups you want to calculate totals, you only need to specify the group.

This expression will calculate all distinct ICD_CODE_LEVEL1_org for each Hospital.

Regards!

Re: dividing single value with total

sagarrahul — Tue, 21 Jun 2016 09:43:02 GMT

not getting can u give eg manuel

Re: dividing single value with total

Anonymous — Tue, 21 Jun 2016 09:54:01 GMT

Hi Sagar,

From QV Help:

Examples of Aggregate Qualifiers

These examples are made with the sum([{set_expression}][distinct][total[<fld {, fld}>]] expression) function, but may be applied on all chart aggregation functions supporting Set Analysis definitions and the total qualifier.

Example 1:

Study the representation of a straight table below, depicting the logical state before any selections have been made:

The second and third expression column have the same number in all rows. This number equals the calculated total in the first expression column.

Now, let's select months 1 and 2 only. The result is as follows:

The result of the third expression (fifth column) remains unchanged because the set definition disregards current selections. The second expression (fourth column) with the total qualifier, shows the new total 10, which is still equivalent to the first expression total (third column).

Example 2:

Study the representation of a straight table below:

In the third expression column (sum(total<Month> Val)) one total is calculated for each month.

In the fourth expression column (sum(total<Grp> Val)) one total is calculated for each group.

Regards!