topic Expression help in QlikView

Expression help

Tue, 17 May 2011 18:32:49 GMT

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

Re: Expression help

johnw — Tue, 17 May 2011 19:48:58 GMT

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))'}

Expression help

Wed, 18 May 2011 13:15:07 GMT

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

Expression help

Wed, 18 May 2011 17:45:57 GMT

Any one pls help.

Thanks