Use variable in expression

[#1]

I have two variables defined

LET vCurrentWeek = Week(Today());

LET vLastWeek = vCurrentWeek-1;

And I have an expression to calculate revenue

=sum({$<Book={"EM EUROBONDS VOL MATCH"}>}IF(PURCHASE_SALE='P',-NOMINAL,NOMINAL)*PRICE/100)

How can I calculate revenue for last week by using my vLastWeek variable and adapting my expression?

[its_anandrjs]

Yes try to convert or may be add week field in the script load.

[MK_QSL]

If you dont have

1) week field or

2) access to script...

you need to use date field...

=SUM({$<Book={'EM EUROBONDS VOL MATCH'},CalendarDate = {'>=$(=WeekStart(Today()-7))<=$(=WeekEnd(Today()-7))'}>}IF(PURCHASE_SALE='P',-NOMINAL,NOMINAL)*PRICE/100)

[#4]

Hi Alex,

Take =max(your_date_field) in text-box .Check your Date format Ex. dd-mm-yyyy ...whatever format it shows simply assign that format using date function like date(Week(Today( ))-7),'Your date format') in expression.

    If your field is in text format then you should change text to date by like this...

      date(num(date#(your_date_field,'existing date format write here')),'DD-MM-YYYY')

=Sum({$<Book={'EM EUROBONDS VOL MATCH'},

Week(date(num(date#(your_date_field,'existing date format write here')),'DD-MM-YYYY')) = {'$(=Week(Today() )-7)'}>}

  IF(PURCHASE_SALE='P',-NOMINAL,NOMINAL)*PRICE/100)

Thanks & regards,

Harshal