topic Re: Updating a set analysis with a variable in QlikView

Updating a set analysis with a variable

elstanford0430 — Fri, 15 Dec 2017 20:09:49 GMT

I am new at this. I have an existing set analysis that I need to update.

The current expression is:

avg({<$(vInpatientCriteria), [HAC Measure] = {'*'}>} [Actual Payments])

$(VinpatientCriteria is equal to a patient type field = I (inpatients)

The set analysis portion of this formula is fine but I need to replace the [Actual Payments} field with a variable formula for Expected Payments ($(Expected Revenue). I have tried everything and searched everywhere and cannot find a way to replace the last field in this expression with a variable in QlikView. Is this possible? If so please help me to write this correctly? I have several graphs that I need to update with this same expression.

Thanks in advance,

ELS

Re: Updating a set analysis with a variable

sunny_talwar — Fri, 15 Dec 2017 20:26:10 GMT

What is the expression for Expected Payment?

Re: Updating a set analysis with a variable

elstanford0430 — Fri, 15 Dec 2017 20:29:24 GMT

Expected Revenue =

sum([Actual Charges]) - $(Adjustments) - sum([U Client CA]) - sum([U Unbilled Interim Billed CA]) - sum([U AETNA CA]) - sum([U Bad Debt CA]) - sum([U Self Pay CA]) - sum([U Medicare Lump]) - sum([U Medicare UB IB]) - sum([U Manual CA]) - sum([U Medicare Wage Index]) - sum([U Reserve CA])

the Adjustments variable above is:

sum({<
[Transaction Classification Code]={'ADJ'}
>}
[Payment Detail Amount])

Re: Updating a set analysis with a variable

sunny_talwar — Tue, 19 Dec 2017 01:56:56 GMT

Would you be able to share a sample?

Re: Updating a set analysis with a variable

qliksus — Tue, 19 Dec 2017 04:21:24 GMT

May be try using the Aggr function

avg({<$(vInpatientCriteria), [HAC Measure] = {'*'}>} Aggr( sum([Actual Charges]) - $(Adjustments) - sum([U Client CA]) - sum([U Unbilled Interim Billed CA]) - sum([U AETNA CA]) - sum([U Bad Debt CA]) - sum([U Self Pay CA]) - sum([U Medicare Lump]) - sum([U Medicare UB IB]) - sum([U Manual CA]) - sum([U Medicare Wage Index]) - sum([U Reserve CA]) , Dim1) )

Re: Updating a set analysis with a variable

elstanford0430 — Tue, 19 Dec 2017 13:24:18 GMT

Thank you for this formula. I have not used Aggr and I am not sure what the "Dim1" at the end is. Should that be replaced by something else? I don't actually have a dimension to add at the end. I need the Expected Revenue variable or formula only.

Re: Updating a set analysis with a variable

elstanford0430 — Tue, 19 Dec 2017 13:25:17 GMT

I am not sure what this request is? All of the fields in the formula for Expected Revenue just contain number values.

Re: Updating a set analysis with a variable

elstanford0430 — Fri, 22 Dec 2017 18:13:58 GMT

I think I was able to get it to work with this very long formula:

sum(

MonthYear =, [Balance Status]={'Z'},