Question on set analysis when adding 2nd dimension

[#1]

I've been staring at this for an hour and can't fathom why this is not working. Probably something simple but could use some help.

have a chart that was working wonderfully for me. Stripping it down to what is not working, I have the following

Dimension: Facility

Expression: sum({$<Facility=,Service=,rptPeriodColonIsDPCText={'Base'}>} total isEligible)

Result: I then filter it to a single Employer = 'SampleEmployer' and get this, accurate, result set:

Facility	Is Eligible
Specialists	14335
Primary Care	14335
Pharmacy	14335
Outpatient	14335
Inpatient	14335
Direct Primary	14335
Total	14335

When I add another dimension called Employer, I want the Expression to be limited to only isEligible per Employer

Dimensions: Facility, Employer

Expression: sum({$<Facility=,Service=,rptPeriodColonIsDPCText={'Base'}>} total <Employer> isEligible)

>> You'll see that I Added the qualifier on the total <Employer>, which I thought would  limit to just the Employer. I expected the same result set as above but instead I receive this:

Employer	Facility	Is Eligible
Sample Employer	Specialists	6016
Sample Employer	Primary Care	6016
Sample Employer	Pharmacy	6016
Sample Employer	Outpatient	6016
Sample Employer	Inpatient	6016
Sample Employer	Direct Primary	6016
Sample Employer	Total	6016

Why does it not return 14335? Any idea what I Am doing wrong here?  I think I've included all pertinent info but lmk if not. Thanks

Eric

[chematos]

sum({$<Facility=,Service=,rptPeriodColonIsDPCText={'Base'}, Employer=>} total isEligible)

[#3]

thanks, Jose, I appreciate the help. I'm still stuck on this. Unfortunately, your answer gives me the sum(isEligible) across all employers, not per employer. I might have cut off some pertinent info in my original question so let me give more detail. The solution you gave gives me 28889 for all cells in the isEligible column below. What I am looking for is the following result:

Employer	Facility	Is Eligible
Sample Employer	Specialists	14335
Sample Employer	Primary Care	14335
Sample Employer	Pharmacy	14335
Sample Employer	Outpatient	14335
Sample Employer	Inpatient	14335
Sample Employer	Direct Primary	14335
Sample Employer	Total	14335
Sample 2	Specialists	10524
Sample 2	Primary Care	10524
Sample 2	Pharmacy	10524
Sample 2	Outpatient	10524
Sample 2	Inpatient	10524
Sample 2	Direct Primary	10524
Sample 2	Total	10524
Sample 3	Specialists	4030
Sample 3	Primary Care	4030
Sample 3	Pharmacy	4030
Sample 3	Outpatient	4030
Sample 3	Inpatient	4030
Sample 3	Direct Primary	4030
Sample 3	Total	4030

Does that help? Any thoughts? I thought I had gotten fairly decent at set analysis but this one is stumping me...

Thanks

Eric

[chematos]

I think I was missing something.

You want to get the sum per employer? 

You only have to miss <Employer> from your expression, that has no sense in the expression, if you add a dimension, the expression will be calculated over that dimension automatically.

Have you tried this?

sum({$<Facility=,Service=,rptPeriodColonIsDPCText={'Base'}>} total isEligible)

[#5]

correct, I want to get the sum(isEligible) per employer, disregarding any selections as well as the Facility dimension. I can probably remove the Service from this example but it is useful in my real world use case.

I tried what you just suggested -

sum({$<Facility=,Service=,rptPeriodColonIsDPCText={'Base'}>} total isEligible)

and it returns the sum(isEligible) across all employers, not the specific...

again, I appreciate your thoughts / help on this. It does seem like i should be able to remove Employer from the expression entirely, given that it is in the dimension, but that isn't happening

[CELAMBARASAN]

Did you checked with Sum({1} isElligible) ?

If not you can check with it..

[chematos]

Could you upload your file or an example to use? That would be usefull...

Try this:

aggr(sum({$<Facility=,Service=,rptPeriodColonIsDPCText={'Base'}>} total isEligible), Employer)

Regards

[MayilVahanan]

HI

I think,

This expression helps, avoid any selection and sum the isEligible for 'Base' only..

= sum({1<rptPeriodColonIsDPCText={'Base'}>} isEligible)

Hope it helps

[#9]

Hi all, so far, despite all the help, it's still not working. A couple of suggestions return the same result set as my original expression, which is less than the expected sum.  In order to problem solve further, I'm going to dig in more and try to find a few specific rows that should be showing up but is not.

Thanks for the help and I'll let everyone know what I find. If I still can't figure it out, I'll post the QVW and see if someone else can solve this brain teaser.

Eric

[CELAMBARASAN]

Can you provide information about the links between Employer , isEligible and Facility?