Updating a set analysis with a variable

[elstanford0430]

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

[sunny_talwar]

What is the expression for Expected Payment?

[elstanford0430]

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])

[sunny_talwar]

Would you be able to share a sample?

[qliksus]

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) )

[elstanford0430]

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.

[elstanford0430]

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

[elstanford0430]

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

sum(

{<

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

[~NumericMonthYear] = {">$(=num(addmonths(max(MonthYear), -12))) <=$(=num(addmonths(max(MonthYear),0))) "}

>}

[Actual Charges])

-

sum(

{<

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

[~NumericMonthYear] = {">$(=num(addmonths(max(MonthYear), -12))) <=$(=num(addmonths(max(MonthYear),0))) "}

>}

[U Client CA])

-

sum(

{<

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

[~NumericMonthYear] = {">$(=num(addmonths(max(MonthYear), -12))) <=$(=num(addmonths(max(MonthYear),0))) "}

>}

[U Unbilled Interim Billed CA])

-

sum(

{<

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

[~NumericMonthYear] = {">$(=num(addmonths(max(MonthYear), -12))) <=$(=num(addmonths(max(MonthYear),0))) "}

>}

[U AETNA CA])

-

sum(

{<

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

[~NumericMonthYear] = {">$(=num(addmonths(max(MonthYear), -12))) <=$(=num(addmonths(max(MonthYear),0))) "}

>}

[U Bad Debt CA])

-

sum(

{<

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

[~NumericMonthYear] = {">$(=num(addmonths(max(MonthYear), -12))) <=$(=num(addmonths(max(MonthYear),0))) "}

>}

[U Self Pay CA])

-

sum(

{<

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

[~NumericMonthYear] = {">$(=num(addmonths(max(MonthYear), -12))) <=$(=num(addmonths(max(MonthYear),0))) "}

>}

[U Medicare Lump])

-

sum(

{<

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

[~NumericMonthYear] = {">$(=num(addmonths(max(MonthYear), -12))) <=$(=num(addmonths(max(MonthYear),0))) "}

>}

[U Medicare UB IB])

-

sum(

{<

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

[~NumericMonthYear] = {">$(=num(addmonths(max(MonthYear), -12))) <=$(=num(addmonths(max(MonthYear),0))) "}

>}

[U Manual CA])

-

sum(

{<

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

[~NumericMonthYear] = {">$(=num(addmonths(max(MonthYear), -12))) <=$(=num(addmonths(max(MonthYear),0))) "}

>}

[U Medicare Wage Index])

-

sum(

{<

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

[~NumericMonthYear] = {">$(=num(addmonths(max(MonthYear), -12))) <=$(=num(addmonths(max(MonthYear),0))) "}

>}

[U Reserve CA])

-

sum({<

[Transaction Classification Code]={'ADJ'},

MonthYear=,

[Balance Status]={'Z'},

[~NumericMonthYear] = {">$(=num(addmonths(max(MonthYear), -12))) <=$(=num(addmonths(max(MonthYear),0))) "}

>}

[Payment Detail Amount])

[elstanford0430]

Now when I try to do the same using "avg" instead of "sum" it will not work.  My second field is null and wipes out the values altogether.  Is there a way around this?