Expression help

[#1]

HI,

Dould you please help me with this expression:

=sum ({$<CALENDAR_DATE={'>=$(=Date(VSTARTDATE))<=$(=Date(VENDDATE))'},GC_BUY_OR_REDEEM-={'B'},

GC_TRANS_ID={$(=aggr(min(GC_TRANS_ID),GC_CARD_NUMBER))}>} GC_BALANCE)

For example for above expression for one gift card:6103300000869935 there are 3 trans_ids

1.05237833-2011-02-14(balance-25)

2.105284498-2011-03-12 20:36:54.000(balance-6.82)

3.105373479-2011-05-08 13:58:17.000(balance-0)

When I select the date rage i.e,calendar_date from 02/01/2011 to 04/30/2011 ,I get the balance as $0 but it should be 6.82 as the last trans_id is on 05-08-2011 which is out of  my date range.

Thanks,

Swetha

[johnw]

I don't think the aggr() portion is producing anything that set analysis can use.  I believe you'd have to use concat() to turn that portion into a quoted list.  I think this:

GC_TRANS_ID={'$(=concat(aggr(min(GC_TRANS_ID),GC_CARD_NUMBER),chr(39)&','&chr(39))'}

[#3]

Hi John,

Thank you for the reply.But when I change the expression as suggested its not giving any value for GC_TRANS_ID and as a result the expression is returning a blank value "-".

Please let me know if any other changes needs to be done to the expression.

Thanks,

Swetha

[#4]

Any one pls help.

Thanks