Help with Set Analysis inside of other Set Analysis

[#1]

Hi Community!

I have a problem using set analysis and I was wondering if you can assist me please. I’ve been dealing with the issue and I can’t this get to work.

I have a data set, and I want to get the latest transaction for a period range.

For example: My period date range is >=09/02/2011 (vStartPPFormat) and <=09/01/2012(vEndPPFormat), therefore my latest value is 192.881 on 8/21/2012. All the fields change constantly, so there’s nothing that I can say on this TC_TXN_TYPE, or on this date.

I don’t seem to find the way to calculate this.

I first calculated the Maximum Date: =Max({<GV_OBJ_ID={619}, CalendarDate={'>=$(vStartPPFormat)<=$(vEndPPFormat)'}>}GV_VALUE_DATE) . With this I got the 8/21/2012 which is correct ,and then I want to use that date in a new expression. I can’t put that expression in a variable, because it will get the max date for all benefits plans, so if some benefit plan has a date of 8/31/2012, it will get me that date, but for this example 8/31/2012 won’t work. I need the maximum date for each benefit plan.

Then I did the following expression to get the 192.881.

MAX( {$<CalendarDate = {"=Max({<GV_OBJ_ID={619}, CalendarDate={'>=$(vStartPPFormat)<=$(vEndPPFormat)'}>}GV_VALUE_DATE)"}>} GV_DOUBLE_VALUE )

And instead of getting the 192.881 I’m getting 193.25, which is the maximum of everything. It’s like it’s not taking in mind the date 8/21/2012

Attached are my files.

Thanks a lot.

P!

[MayilVahanan]

HI

try like this

=MAX( {$<CalendarDate = {"$(=Max({<GV_OBJ_ID={619}, CalendarDate={'>=$(vStartPPFormat)<=$(vEndPPFormat)'}>}GV_VALUE_DATE))"}>} GV_DOUBLE_VALUE )

[CELAMBARASAN · Accepted solution]

Hi,

try this

FirstSortedValue( {<GV_OBJ_ID={619}, CalendarDate={'>=$(vStartPPFormat)<=$(vEndPPFormat)'}>} Aggr(Max(GV_DOUBLE_VALUE), BenefitPlan, CalendarDate) , -Aggr(CalendarDate,BenefitPlan,CalendarDate))

[#4]

Hi Celambarasam,

The FirstSortedValue function worked. What I don't understand is why you have to aggregate?

Thanks a lot!

[#5]

Hi Mayil,

This will work only when you click on a specific contract, if you clear the selections it won't work. I've tried this before.

Thanks.