Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Ignore Some Dimensions in an Expression

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.

5 Replies
maleksafa
Valued Contributor

Re: Ignore Some Dimensions in an Expression

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

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

Not applicable

Re: Re: Ignore Some Dimensions in an Expression

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

Not applicable

Re: Re: Ignore Some Dimensions in an Expression

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
Valued Contributor II

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

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

Community Browser