Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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?
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.
I don't see any issue with your expression if WeekStart is working.... Although, did you use MonthsStart or MonthStart here?
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.
WeekStart is not working but MonthStart is working.
I used MonthsStart in my expression.
Would you be able to share a sample?
It gives the same result if MonthStart is used
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)
Thank you very much Sunny.
Do you know any reason why the keyword Month is available, but the keyword Week is not available?