topic Re: Question on set analysis when adding 2nd dimension in QlikView

Question on set analysis when adding 2nd dimension

Mon, 15 Oct 2012 23:16:24 GMT

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

Re: Question on set analysis when adding 2nd dimension

chematos — Tue, 16 Oct 2012 13:21:55 GMT

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

Re: Question on set analysis when adding 2nd dimension

Tue, 16 Oct 2012 16:33:55 GMT

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

Re: Question on set analysis when adding 2nd dimension

chematos — Tue, 16 Oct 2012 16:45:58 GMT

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)

Re: Question on set analysis when adding 2nd dimension

Tue, 16 Oct 2012 17:29:45 GMT

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

Re: Question on set analysis when adding 2nd dimension

CELAMBARASAN — Tue, 16 Oct 2012 17:32:23 GMT

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

If not you can check with it..

Re: Question on set analysis when adding 2nd dimension

chematos — Tue, 16 Oct 2012 17:36:06 GMT

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

Re: Question on set analysis when adding 2nd dimension

MayilVahanan — Tue, 16 Oct 2012 18:01:15 GMT

I think,

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

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

Hope it helps

Re: Question on set analysis when adding 2nd dimension

Wed, 17 Oct 2012 02:55:11 GMT

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

Re: Question on set analysis when adding 2nd dimension

CELAMBARASAN — Wed, 17 Oct 2012 12:42:51 GMT

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