Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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.