Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

akuttler
Contributor

Conditional set modifiers

Hello I have the following expression to find net collection rate: sum of payments/sum of allowed.

(Sum({<Account = {'2000','2001','2002','2003','2004'}>} Payments)*-1) / (SUM(AmountAllowed))

Fields:

ID

Encounter id

Account (insurance, patient or write off)

Payments

Amount Allowed

However, I need to sum payments only for encounter id's  where that have an allowed amount posted (>0)..even if its on another row.

in other words, if a patient is self pay and no allowed amount was ever posted from an insurance check, I don't want to include those payments.

Hope that makes sense.

Tags (2)
1 Solution

Accepted Solutions
sunilkumarqv
Valued Contributor II

Re: Conditional set modifiers

somthing like below

if(sum(Amount)>0 and sum(Amountallowed),ID)

6 Replies

Re: Conditional set modifiers

Would you be able to elaborate this with examples?

akuttler
Contributor

Re: Conditional set modifiers

For example...for some encounter id's...there is an insurance allowed amount posted but for some encounters there is no allowed amount posted because it did not process through insurance. so there will only be patient payments

There are two Account (payment) types: insurance & patient.

I want to sum payments (insurance and patient) for only encounters that processed through insurance and have an allowed amount posted somewhere in the table.

Re: Conditional set modifiers

Few rows of mocked up data might be helpful to understand what you have

akuttler
Contributor

Re: Conditional set modifiers

Capture.PNG

akuttler
Contributor

Re: Conditional set modifiers

So I want to filter for encounter id's where there exists an AmountAllowed and sum payments.

sunilkumarqv
Valued Contributor II

Re: Conditional set modifiers

somthing like below

if(sum(Amount)>0 and sum(Amountallowed),ID)

Community Browser