topic Re: Week To Date issue - what is missing? in QlikView

Week To Date issue - what is missing?

Tue, 11 Apr 2017 14:39:02 GMT

I am using the calendar that the source data uses, which is:

year, quarter, month_no_id, week_no_id, calendar_date (datetime) and have added date_id, week, month_no, monthName and day .

For a given selection of year, month_no, week_no and day:

=sum({$< day = {$(#=Only(day ))},week_no_id=, month_no_id=>} sales ) works correctly

=Sum({<Year=, Month=, Quarter=, Week_no=, day=, date_id={'>=$(=Num(MonthsStart(Max(date_id))))<=$(=Max(date_id))'}>} sales)

also works correctly to give the Month to date.

but

=Sum({<Year=, Month=, Quarter=, Week_no=, day=, date_id={">=$(=Num(WeekStart(Max(date_id))))<=$(=Max(date_id))"}>} sales)

does not give the correct figure.

Also, changing the "Year=" to "year=" gives a different result.

What am I missing here?

Re: Week To Date issue - what is missing?

sunny_talwar — Tue, 11 Apr 2017 14:46:45 GMT

Can you check in a text box object what do you get when you use this

=WeekStart(Max(date_id)) - > Do you get the right date which you want to use in your expression?

Re: Week To Date issue - what is missing?

Tue, 11 Apr 2017 14:51:30 GMT

Thanks Sunny.

I didn't think of that way to check the week start, and the start of the week it correct, it is is the Monday before the date_id.

Re: Week To Date issue - what is missing?

sunny_talwar — Tue, 11 Apr 2017 14:54:08 GMT

I don't see any issue with your expression if WeekStart is working.... Although, did you use MonthsStart or MonthStart here?

Re: Week To Date issue - what is missing?

Tue, 11 Apr 2017 15:15:57 GMT

The QlikView keyword "Month" works in the expressions, but the keyword "Week" does not.

Maybe this is the reason, but I can't see how it works.

Re: Week To Date issue - what is missing?

Tue, 11 Apr 2017 15:18:19 GMT

WeekStart is not working but MonthStart is working.

I used MonthsStart in my expression.

Re: Week To Date issue - what is missing?

sunny_talwar — Tue, 11 Apr 2017 15:23:04 GMT

Would you be able to share a sample?

Re: Week To Date issue - what is missing?

Tue, 11 Apr 2017 15:29:20 GMT

It gives the same result if MonthStart is used

Re: Week To Date issue - what is missing?

sunny_talwar — Tue, 11 Apr 2017 15:39:39 GMT

I see no issues with this expression, unless there is not data for sales for the date range you are trying to get

=Sum({<Year=, Month=, Quarter=, Week_no=, day=, date_id={">=$(=Num(WeekStart(Max(date_id))))<=$(=Max(date_id))"}>} sales)

Re: Week To Date issue - what is missing?

Tue, 11 Apr 2017 15:42:38 GMT

Thank you very much Sunny.

Do you know any reason why the keyword Month is available, but the keyword Week is not available?

Re: Week To Date issue - what is missing?

sunny_talwar — Tue, 11 Apr 2017 15:45:06 GMT

Which Month are you talking about?

=Sum({<Year=, Month=, Quarter=, Week_no=, day=, date_id={'>=$(=Num(MonthsStart(Max(date_id))))<=$(=Max(date_id))'}>} sales)

Re: Week To Date issue - what is missing?

Tue, 11 Apr 2017 15:49:14 GMT

I am talking about the first one - the one in Red in your post.

The keyword Week is not available to use.

Re: Week To Date issue - what is missing?

sunny_talwar — Tue, 11 Apr 2017 15:51:37 GMT

Do you have a field called Week in your dashboard? May be you have Month field, but no Week field which is why you don't see week....although you did add Week_No in your expression..... Set analysis works with fields (mostly) from your back end

Re: Week To Date issue - what is missing?

Tue, 11 Apr 2017 16:30:32 GMT

Yes, that is it. Thanks again Sunny.

Re: Week To Date issue - what is missing?

Wed, 19 Apr 2017 10:39:05 GMT

Thanks again Sunny.

This is what ended up working for the Week To Date (which won't display at all if a Week is not selected and won't display the WTD if a day is not selected):

If(ISNULL(GetFieldSelections(day)),sum({$<week_no= {$(#=Only(week_no))}, month_no_id=>} no_of_units) ,If(ISNULL(GetFieldSelections(week_no)), 0, Sum({<Month_no=, day=, month_no_id, date_id={">=$(=Num(WeekStart(Max(date_id))))<=$(=Max(date_id))"}>} no_of_units)))

and for the MTD, which won't display unless a month is selected and won't display the MTD unless a day is selected:

If(ISNULL(GetFieldSelections(day)),

If(ISNULL(GetFieldSelections(week_no)), sum({$<month_no = {$(#=Only(month_no))}, week_no=>} no_of_units),Sum({<week_no=, day=, date_id={">=$(=Num(MonthStart(Max(date_id))))<=$(=Max(date_id))"}>} no_of_units)),

Sum({<week_no=, day=, date_id={">=$(=Num(MonthStart(Max(date_id))))<=$(=Max(date_id))"}>} no_of_units))