topic Re: Last week set analysis in QlikView

Last week set analysis

Wed, 16 Apr 2014 15:30:42 GMT

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

Re: Last week set analysis

santharubban — Wed, 16 Apr 2014 15:49:44 GMT

Hi try this,

=sum({$<Book={"EM MATCH"},w_date = {'$(= weekstart(w_date))'}>}PRICE)

Re: Last week set analysis

Wed, 16 Apr 2014 15:53:05 GMT

I assume i substitute my CalendarDate field for w_date?

=sum({$<Book={"EM MATCH"},CalendarDate = {'$(= weekstart(CalendarDate))'}>}PRICE)

this did not work

Re: Last week set analysis

Wed, 16 Apr 2014 15:54:11 GMT

I am unclear how your suggestion would apply to the previous working week, which is what I need

Re: Last week set analysis

Wed, 16 Apr 2014 16:05:40 GMT

I assume a -1 needs to be added to specify the previous week. Although this still doesn't work!

=sum({$<Book={"EM MATCH"},CalendarDate = {'$(= weekstart(CalendarDate),-1)'}>}PRICE)

Re: Last week set analysis

rustyfishbones — Wed, 16 Apr 2014 16:12:45 GMT

Hi Alex,

Can you share part of the App, or some of the Data?

Re: Last week set analysis

MK_QSL — Wed, 16 Apr 2014 16:13:36 GMT

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

Re: Last week set analysis

Wed, 16 Apr 2014 16:18:48 GMT

Alan, ignoring the Book field, its just a CalendarDate field and an amount field (aka PRICE)

There are multiple amount entries for each date

Re: Last week set analysis

Wed, 16 Apr 2014 16:20:03 GMT

That didn't seem to work Manish

Re: Last week set analysis

MK_QSL — Wed, 16 Apr 2014 16:27:03 GMT

What is the format of your Date Field?

Re: Last week set analysis

Wed, 16 Apr 2014 16:31:14 GMT

It looks like its

DD-MMM-YYYY

Re: Last week set analysis

MK_QSL — Wed, 16 Apr 2014 16:35:11 GMT

Try This....

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

Re: Last week set analysis

Wed, 16 Apr 2014 16:39:52 GMT

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)

Re: Last week set analysis

MK_QSL — Wed, 16 Apr 2014 16:53:17 GMT

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.

Re: Re: Last week set analysis

Thu, 17 Apr 2014 07:23:41 GMT

Hi Manish,

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

I have attached a set of sample data

Re: Re: Last week set analysis

MK_QSL — Thu, 17 Apr 2014 07:26:26 GMT

Need your sample apps or sample data file..

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

Re: Re: Last week set analysis

Thu, 17 Apr 2014 07:26:58 GMT

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)

Re: Last week set analysis

Thu, 17 Apr 2014 07:29:49 GMT

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)

Re: Re: Last week set analysis

Thu, 17 Apr 2014 07:36:50 GMT

i have attached the sample data in the reply above

Re: Re: Last week set analysis

Thu, 17 Apr 2014 07:42:22 GMT

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?