topic Having Difficulty Excluding Dimensions in Set Analysis in QlikView

Having Difficulty Excluding Dimensions in Set Analysis

Wed, 19 Sep 2012 21:03:37 GMT

I have looked through Numerous examples but I cannot get this to work.

I current have the following Dimesions:

Carrier

Family Id

Mem Id

Service Date

Mem Paid

Plan Paid

I then have to calculate Plan Paid for Service Dates that are greater than an eligibility date and am trying to use a set analysis to sum it to a Member Level

sum

(
{$< [Service Date]={">$(=max(total <[Carrier],[Mem Id]> [Eligibility Date]))"},
[Service Date] = , [Plan Paid] = , [Mem Paid]>} [Plan Paid] )

But this does not sum at the Mem Id level, it is suming each individual line. I have tried adding aggrs and totals but cannot seem to get this to work. Any ideas?

thanks.

Re: Having Difficulty Excluding Dimensions in Set Analysis

swuehl — Wed, 19 Sep 2012 22:36:26 GMT

First, I think you shouldn't use [Service Date] twice in your set modifier list (what do you want to achieve herewith?).

If you want to aggregate up to a certain level of your dimensions (i.e. dimensionality() ), you could use a total with a field list (similar to what you used in the advanced search for Service Date set modifier).

So maybe like

=sum( total<Carrier, [Family Id], [Mem Id]> {$<[Service Date]={">$(=max([Eligibility Date]))"}>} [Plan Paid])

You see, I am also not sure about the advanced search in the [Service Date] set modifier. The dollar sign expansion and the set expression is evaluated once per chart, not per dimension value. Do you need to calculate the max Eligibility Date per current Carrier and Mem Id dimension value?

Maybe some sample lines of data together with your expected outcome help to understand your requirements.

Regards,

Stefan

Re: Having Difficulty Excluding Dimensions in Set Analysis

Thu, 20 Sep 2012 14:07:50 GMT

thanks for the reply swuehl. I did not realize that the $ expansion is evaluated once per chart so that is a BIG help. I am needing to calculate the eligilibty by the member.

That might lead me in the right direction knowing that. If not I will try to mock up some data to post. I appreciate the help.