Expression Question

[danielnevitt]

Hi,

I have the attached Straight table.

Dimensions = trade_dt and period_cd_my

Expression = Sum(pos_lots)

Is there a way to show the sum of the position lots where the trade_dt is less than or equal to the dimension trade_dt?

ie Sum({<trade_dt={'<=trade_dt'}>}pos_lots)

Any help will be much appreicated.

Regards,

Daniel

[Kushal_Chawda]

I would suggest to go for script solution because on selection front end solution will not work properly and would be very complex to get it work. Best practice always says that, you should handle most of the condition in script if it is possible

[PabloOrtiz]

I agree whith you Kushal.

[johnw]

This sounds like a good place to use an AsOf table. We can build a table that links each AsOfDate to that and every previous trade_dt. It lets you avoid doing accumulations in the script while still solving the problem with script. There are a lot of ways to generate such a table. This is one.

AsOf:
LOAD date(fieldvalue('trade_dt',recno())) as trade_dt
AUTOGENERATE fieldvaluecount('trade_dt')
;
LEFT JOIN (AsOf)
LOAD trade_dt as AsOfDate
RESIDENT AsOf
;
INNER JOIN (AsOf)
LOAD *
RESIDENT AsOf
WHERE trade_dt <= AsOfDate
;