topic Re: trying to subtract one column from another in QlikView

trying to subtract one column from another

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

I am trying to subtract one calculated column from another calculated column. The individual lines work but not totals. Please help - thanks

My formulas for column Work RVU's is = sum (Units * WorkRVU)

Units is sum(Units)

Gross Charges is sum (CalculatedCharge)

Exclusions is =if ("Work RVU's" =0, Sum (CalculatedCharge),0)

and the last column is = GrossCharges - Exclusions

Re: trying to subtract one column from another

Anonymous — Tue, 28 Feb 2017 00:14:31 GMT

Looks like total works as well, but you use different formats.

Re: trying to subtract one column from another

sunny_talwar — Tue, 28 Feb 2017 04:04:49 GMT

May be try to replace your expression for Exclusions

Sum(Aggr(If(Sum(Units * WorkRVU) = 0, Sum(CalculatedCharge), 0), <YourDimensionsSeparatedByCommaHere>))

Re: trying to subtract one column from another

Anil_Babu_Samineni — Tue, 28 Feb 2017 04:37:12 GMT

For me also it's working. Are you talking about Decimal change. And use round if you are looking that way or Simple go to number tab and then use Integer

Re: trying to subtract one column from another

rahulpawarb — Tue, 28 Feb 2017 06:26:35 GMT

Hello Carol,

Number Format Settings (Number Tab) for Gross Charges, Exclusions (Integer) and last column (Fixed to 2 Decimals) is causing this issue. Make the formatting consistent across all three columns and get the desired results.

Hope this will be helpful.

Regards!

Rahul

Re: trying to subtract one column from another

cquesenbery — Tue, 28 Feb 2017 16:59:14 GMT

All formats now the same but the column for exclusions will still not add up - it is like it doesn't like my exclusion formula even though the line items works. Exclusions does not add up and the last column gross charges minus exclusions each line add up but not the total. I tried Sunny's formula but that didn't work.

Re: trying to subtract one column from another

Anil_Babu_Samineni — Wed, 01 Mar 2017 03:01:34 GMT

May be buzy, Sunny intention is this?

Check --> Sum(Aggr(If(Sum(Units * WorkRVU) = 0, Sum(CalculatedCharge), 0), [CPT Code]))

Or provide sample to work on it so that we can help you out

Re: trying to subtract one column from another

sunny_talwar — Wed, 01 Mar 2017 09:10:29 GMT

Would you be able to share a sample?