Ignore Some Dimensions in an Expression

[#1]

I have created the expression "MM's", which is a membership field in my pivot table. In the image below, the top field contains the correct membership numbers. However, when I add claim fields in front of my population field ("Benchmarks"), it looks to only be counting members WITH a claim in that field.

Here is the MM's formula I am using for the first chart, which works:

sum({1 <BENCHMARKS= P(BENCHMARKS), INCURRED_YEAR= P(INCURRED_YEAR), INCURRED_YEAR_AND_MONTH= P(INCURRED_YEAR_AND_MONTH)>} MM_UNITS)

Here is the MM's formula I am using for the second chart, which does NOT seem to work:

sum (Total <INCURRED_YEAR_AND_MONTH, INCURRED_YEAR, BENCHMARKS> MM_UNITS)

• My goal with this formula is to sum MM_Units IGNORING all dimensions except: INCURRED_YEAR_AND_MONTH, INCURRED_YEAR, and BENCHMARKS.
• But it looks like it is still considering my claim fields: HCG_HIP_HOP and HCG_MR_LINE_ROLLUP_DESCR

Thank you in advance.

[maleksafa]

sum ({<HCG_HIP_HOP =,HCG_MR_LINE_ROLLUP_DESCR=>}Total <INCURRED_YEAR_AND_MONTH, INCURRED_YEAR, BENCHMARKS> MM_UNITS)

maybe something like that to ignore the other 2 dimensions.

[Colin-Albert]

Your total expression

     sum (Total <INCURRED_YEAR_AND_MONTH, INCURRED_YEAR, BENCHMARKS> MM_UNITS)

is telling QlikView to sum MM_UNITS grouped by INCURRED_YEAR_AND_MONTH and INCURRED_YEAR, BENCHMARKS

If you want to ignore all dimensions except INCURRED_YEAR_AND_MONTH, INCURRED_YEAR, and BENCHMARKS, then firstly do no include those other dimensions in your chart.

If you use the {1} set in your expression, then all dimensions are ignored.

     sum ( {1} MM_UNITS)

Just use the BENCHMARKS & & YEAR/MONTH dimensions in your chart

[#4]

Hi Colin,

I do need the other dimensions (HCG_HIP_HOP, HCG_MR_LINE_ROLLUP) in order to complete the rest of the breakouts in my analysis.

After my membership column, I will calculate Paid PMPM (per member per month) and Allowed  PMPM broken out by these claim dimensions.

• i.e. 'Paid PMPM' will be this: sum([AMT_PAID])/([MM's])
• AMT_PAID needs to change with the claim dimensions HCG_HIP_HOP, HCG_MR_LINE_ROLLUP
• MM's (Membership) needs to ignore all dimensions except: Benchmarks, Incurred_year, Incurred_year_and_month
• This combination will allow me to do the "per member per month" (PMPM) calculations

Thanks again,

Alysha

[#5]

Hi Malek,

Thanks for the response! I tried your suggestion, however, it still seems to be taking the first 2 membership columns into account.

[chematos]

Have you tried with aggr() function? I am not sure this works in a pivot table...

aggr(sum (Total  MM_UNITS),INCURRED_YEAR, BENCHMARKS)

Regards,

Jose