topic Re: Help with Set Analysis inside of other Set Analysis in QlikView

Help with Set Analysis inside of other Set Analysis

Thu, 20 Dec 2012 19:03:30 GMT

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.

Re: Help with Set Analysis inside of other Set Analysis

MayilVahanan — Thu, 20 Dec 2012 21:12:09 GMT

try like this

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

Re: Help with Set Analysis inside of other Set Analysis

CELAMBARASAN — Thu, 20 Dec 2012 21:23:29 GMT

Hi,

try this

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

Re: Help with Set Analysis inside of other Set Analysis

Thu, 03 Jan 2013 19:22:13 GMT

Hi Celambarasam,

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

Thanks a lot!

Re: Help with Set Analysis inside of other Set Analysis

Thu, 03 Jan 2013 19:22:53 GMT

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.