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?

[MK_QSL]

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

[#3]

I don't have a field called Week

how is that defined?

[MK_QSL]

Write below in script below your DateField

Week(YourDateField) as Week

[#5]

I don't have access to the load script. I'm am doing a binary load from another qvw

[its_anandrjs]

You can try write

Add this script to the expression Week(YourDateFieldHere) = {'$(=Week(Today() )-7 )'

=Sum({$<Book={"EM EUROBONDS VOL MATCH"}, Week(YourDateFieldHere) = {'$(=Week(Today() )-7 )'}>}

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

[#7]

I get a blank result when I do that Anand

[#8]

Does my date field need to be in a certain format for that to work?

In my list boxes it appears as DD-MMM-YYYY

[its_anandrjs]

Try like

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

[#10]

It didn't work either. Blank result again

Is it possible my CalendarDate field may not be in the correct format, even though it displays correctly in the list box and tables? Would that make a difference?