Last week set analysis

[#1]

How can i use the Weekstart function within a set analysis, so that only the data for last week is included? (I do not mean the last 7 days, i mean the preceding working week.) In this case, I can't adjust my master calendar so have to make do with the CalendarDate field.

This is my expression

=sum({$<Book={"EM MATCH"}>}PRICE)

I can't get my head round the correct syntax

[#2]

It looks like its

DD-MMM-YYYY

[MK_QSL]

Try This....

=SUM({$<Book={'EM MATCH'},[YourDateField] = {'>=$(=Date(WeekStart(Today()-7),'DD-MMM-YYYY')<=$(=Date(WeekEnd(Today()-7),'DD-MMM-YYYY')'}>}PRICE)

[#4]

Again, no luck. I was trying to keep the questions as simple as possible and therefore didn't mention the IF statment at the end. Would that make a difference? This is the whole expression

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

[MK_QSL]

The above portion is working in my apps, so there should be some problem at your end.

If you could able to upload your sample apps or sample data file, would be great to help you out.

[#6]

Hi Manish,

i tried that again but it just says 'No data to display'

I have attached a set of sample data

[MK_QSL]

Need your sample apps or sample data file..

it is difficult to just guess and answer your question at this stage now.

[#8]

Manish, just so you know, the expression without the 'last week' part works fine

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

[#9]

In your script, make sure the working week is a field in your calendar. It's extremely inconvenient that you cannot change the script. If you could, you would be able do the following, which would make for a very logical and clean solution imho.

Then, also in the script, define the current and/or last working week as a variable, e.g.:

LET vCurrentWeek = Week(Today());

LET vLastWeek = vCurrentWeek-1;

Then, in your visualization, use:

=sum({$<Book={"EM MATCH"}, Week={$(vLastWeek)}>} PRICE)

To make it a little more advanced (using only the current year).

In Calendar table:

Dual(WeekYear(CalendarDate ) & '-' & Week(CalendarDate ), WeekStart(CalendarDate )) AS YearWeek

LET vCurrentWeek = WeekStart(Today());

LET vLastWeek = vCurrentWeek-7;

In visualization:

=sum({$<Book={"EM MATCH"}, YearWeek={$(vLastWeek)}>} PRICE)

[#10]

i have attached the sample data in the reply above

[#11]

Thanks Dirk, for reason I won't go into, I'm doing a binary load of another qvw and can't change the underlying calendar at the minute.

Would that idea still work if I added the variable via the 'Variable Overview' option on the Setting tab?