topic Re: Re: Ignore Some Dimensions in an Expression in QlikView

Ignore Some Dimensions in an Expression

Mon, 27 Oct 2014 15:25:03 GMT

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.

Re: Ignore Some Dimensions in an Expression

maleksafa — Mon, 27 Oct 2014 15:27:20 GMT

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.

Re: Ignore Some Dimensions in an Expression

Colin-Albert — Mon, 27 Oct 2014 15:35:23 GMT

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

Re: Re: Ignore Some Dimensions in an Expression

Mon, 27 Oct 2014 16:01:56 GMT

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

Re: Re: Ignore Some Dimensions in an Expression

Mon, 27 Oct 2014 16:03:18 GMT

Hi Malek,

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

Re: Re: Re: Ignore Some Dimensions in an Expression

chematos — Mon, 27 Oct 2014 17:59:04 GMT

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