topic Problem with week to date expression in QlikView

Problem with week to date expression

OmarBenSalem — Tue, 02 Feb 2016 10:04:33 GMT

Hi all,

I have a trouble with the WeekToDate expression.

In fact, the values returned when I use the WTD are the same returned with the MTD..

As if the WeekStart function acts like the MonthStart function and returns the debut of each month.

Here are the expressions I used for each case:

WTD:

Sum({<Year=, Month=, Quarter=, [Date Ventes]= , DateNum={">=$(=Num(WeekStart(Max(DateNum))))<=$(=Max(DateNum))"}>} [Chiffre d'affaires])

WTD-1 :

Sum({<Year=, Month=, Quarter=, [Date Ventes]= , DateNum={">=$(=Num(WeekStart(Max(DateNum),-52)))<=$(=Num(addyears(max([Date Ventes]),-1)))"}>} [Chiffre d'affaires])

MTD :

Sum({<Year=, Month=, Quarter=, [Date Ventes]= , DateNum={">=$(=Num(MonthStart(Max(DateNum))))<=$(=Max(DateNum))"}>} [Chiffre d'affaires])

MTD-1 :

Sum({<Year=, Month=, Quarter=, [Date Ventes]= , DateNum={">=$(=Num(MonthStart(Max(DateNum),-12)))<=$(=Num(addyears(max([Date Ventes]),-1)))"}>} [Chiffre d'affaires])

I have yet another question: what's the purpose of "Year=, Month=, Quarter=, [Date Ventes]=" ? I know that if I delete them and then filter by dte, I won't have the same result. But why?

Thank you !

Re: Problem with week to date expression

sunny_talwar — Tue, 02 Feb 2016 10:28:43 GMT

Is your Max(DateNum) happens to be Feb 2nd 2016? Because if that is the case then you MTD and WTD will be exactly the same because there is 2 days WTD (1st and 2nd Feb) and 2 days in MTD (1st and 2nd Feb)

To answer your second question, this is how you specify which field selections should not change anything in the chart. The issue is that if you don't add them, Date or Month selection will further filter out from your date range specified in the DateNum field. You don't want that to happen so you restrict any other date field selection and allow it to move just on DateNum

Re: Problem with week to date expression

prieper — Tue, 02 Feb 2016 10:29:24 GMT

Why do you deduct 52? Think you need to have the Weeknumber of the previous year?

The command "Year=, Month=, Quarter=, [Date Ventes]=" means that the function shall disregard all entries made in these fields.

Peter

Re: Problem with week to date expression

OmarBenSalem — Tue, 02 Feb 2016 13:11:47 GMT

No, the max(dateNum) returns the num of the date I select to filter with. For example,

Re: Problem with week to date expression

OmarBenSalem — Tue, 02 Feb 2016 13:13:11 GMT

Yes, I think the year contains 52 weeks; so I deduct 52, It would happen to be the same week of the previous year, no?

Re: Problem with week to date expression

OmarBenSalem — Tue, 02 Feb 2016 13:15:03 GMT

I added the Max to have a result even though I didn't make a selection

Re: Problem with week to date expression

prieper — Tue, 02 Feb 2016 14:01:33 GMT

From the above code it seems that in your expression

.... DateNum={">=$(=Num(WeekStart(Max(DateNum),-52)))....

DateNum is a Date and the whole expression refers to a date, so deducting 52 ends with a date approx 2 monthes in the past.

For the calculation with weeks it seems to be easier to work with rolling weeks, like

Year(MyDate)*52 + Week(MyDate) AS WeekNum

Doing so might make your life a bit easier

Peter

Re: Problem with week to date expression

swuehl — Tue, 02 Feb 2016 14:18:16 GMT

For the WTD-1, I would probably use AddYears also in the lower limit:

DateNum={">=$(=Num(WeekStart(AddYears(Max(DateNum),-1))))<=$(=Num(addyears(max([Date Ventes]),-1)))"}

though this selection will show one to two days less dates than WTD, so may want to modify the upper limit.

can't see why WTD and MTD should return same values (unless for specific DateNum selections, as mentioned above.

If you remove the labels of your chart expressions, then hover with the mouse over the labels, the expression will be shown with dollar sign expansions expanded. What do the two expressions show in the field modifier ranges?

Re: Problem with week to date expression

OmarBenSalem — Tue, 02 Feb 2016 14:53:34 GMT

Here is a screen shot that resumes the confusing part ! The expression i'm using are correct, the result should differ but I keep on having the same result !!

I really I'm confused !

Re: Problem with week to date expression

OmarBenSalem — Tue, 02 Feb 2016 15:14:28 GMT

here is another example :

Re: Problem with week to date expression

swuehl — Tue, 02 Feb 2016 16:56:01 GMT

1st and 2nd august 2015 were Sat / Sun.

These two dates are the difference between WTD and MTD

Do you have values to aggregate for weekends?