topic Re: Divide Total Row in QlikView

Divide Total Row

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

I need to take the Sum of one total and divide it by the other, but it ends up blank. I need 412.8 / 146.25 = 2.82

Expression for weighted is: Sum(Total [Sum wRVU in FY for each code])/SUM( Total [Adjustment if CPT Not Hourly])

CPT Code	# of units billed in FY	Adjustment if CPT Not Hourly	wRVU	Sum wRVU in FY for each code	Weighted average wRVU per unit billed
90791	4	4.00	3.00	12.0
90832	3	1.50	1.50	4.5
90834	5	3.75	2.00	10.0
90837	2	0.40	3.00	6.0
96150	236	35.40	0.50	118.0
96151	131	19.65	0.48	62.9
96152	295	44.25	0.46	135.7
96154	121	18.15	0.45	54.5
96155	21	3.15	0.44	9.2
NOCHG	16	16.00	0.00	0.0
Total	834	146.25	11.83	412.8	0.00

Re: Divide Total Row

Anil_Babu_Samineni — Tue, 30 Jan 2018 00:27:10 GMT

What formula used for?

Re: Divide Total Row

Siva_Sankar — Tue, 30 Jan 2018 01:05:57 GMT

Use the expression to get your expected output sum([Sum wRVU in FY for each code])/sum([Adjustment if CPT Not Hourly]).

I got your expected answer, refer the screenshot and attached qlikview file.

Re: Divide Total Row

shiveshsingh — Tue, 30 Jan 2018 06:29:53 GMT

PFA

Re: Divide Total Row

shiveshsingh — Tue, 30 Jan 2018 06:30:13 GMT

Re: Divide Total Row

proctors — Tue, 30 Jan 2018 12:58:33 GMT

I'm getting a bad field name error. I'll post the file. (repost with SA off)

Re: Divide Total Row

proctors — Tue, 30 Jan 2018 12:59:01 GMT

Still getting different number. See file I attached to my earlier post.

Re: Divide Total Row

sunny_talwar — Tue, 30 Jan 2018 13:03:30 GMT

Please remove the section access from the attached file.

Re: Divide Total Row

proctors — Tue, 30 Jan 2018 13:14:10 GMT

Sorry, just reposted above.

Re: Divide Total Row

sunny_talwar — Tue, 30 Jan 2018 13:29:32 GMT

May be this

Round(Sum(Aggr(Sum([Charge Qty])*(Sum([RVU WORK])/Sum([Charge Qty])), [CPT Code])), 0.1)/

Round(Sum(Aggr(IF([CPT Code]='90837', (Sum([Charge Qty]*0.2)),

IF([CPT Code]='90832', (Sum([Charge Qty]*0.5)),

IF([CPT Code]='90834', (Sum([Charge Qty]*0.75)),

IF([CPT Code]='96150', (Sum([Charge Qty]*0.15)),

IF([CPT Code]='96151', (Sum([Charge Qty]*0.15)),

IF([CPT Code]='96152', (Sum([Charge Qty]*0.15)),

IF([CPT Code]='96153', (Sum([Charge Qty]*0.15)),

IF([CPT Code]='96154', (Sum([Charge Qty]*0.15)),

IF([CPT Code]='96155', (Sum([Charge Qty]*0.15)), Sum([Charge Qty])))))))))), [CPT Code])), 0.01)

Re: Divide Total Row

proctors — Tue, 30 Jan 2018 13:47:37 GMT

Works. What did you do?!

Re: Divide Total Row

sunny_talwar — Tue, 30 Jan 2018 13:50:48 GMT

Used Sum(Aggr()) to get the totals Sum of rows in pivot tables ‒ QlikView and used original expressions instead of column reference as they are not available within aggr function

Re: Divide Total Row

qlik4asif — Wed, 31 Jan 2018 05:49:33 GMT

Can't we use Column(2)/Column(4)

taking ur original post as reference