topic Re: "Floating" calendar issue in QlikView

"Floating" calendar issue

marko_rakar — Mon, 23 Nov 2015 11:55:25 GMT

Questions;

I have situation where I need to compare day to day from a previous time period (year, quarter, month); but since this is retail related, it is really important to match weekdays in my presentation (like first sunday of 2014 should align with first sunday of 2015...); how is this done?
Extension to the idea above is what happens if you have floating holidays (like easter holidays), they move wildly from year to year and they are major retail events so I need somehow to match those as well. Any ideas, examples?

Re: "Floating" calendar issue

HirisH_V7 — Mon, 23 Nov 2015 13:21:16 GMT

Hi Marko,

You can use the following Expressions Based on Your Requirement,

YTD Sales (Year To Date)

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=YearStart(Today()))<=$(=Today())’}>} Sales )

Note: Year=, Quarter=, Month=, Week= excludes the selections in Year, Quarter, Month and Week dimensions.

QTD Sales (Quarter To Date)

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=QuarterStart(Today()))<=$(=Today())’}>} Sales)

MTD Sales (Month To Date)

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=MonthStart(Today()))<=$(=Today())’}>} Sales)

WTD Sales (Week To Date)

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=WeekStart(Today()))<=$(=Today())’}>} Sales)

Last 5 Years Sales

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=YearStart(Today(), -4))<=$(=Today())’}>} Sales )

Last 6 Quarters Sales

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=QuarterStart(Today(), -5))<=$(=Today())’}>} Sales )

Last 12 Months Sales

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=MonthStart(Today(), -11))<=$(=Today())’}>} Sales )

Last 15 Weeks Sales

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=WeekStart(Today(), -14))<=$(=Today())’}>} Sales )

Last 10 Days Sales

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=Date(Today()-9))<=$(=Today())’}>} Sales )

Yesterday Sales

Sum({<Year=, Quarter=, Month=, Week=, Date={‘$(=Date(Today()-1))’}>} Sales )

I Hope this Helps,

Regards,

HirisH

Re: "Floating" calendar issue

marcus_sommer — Mon, 30 Nov 2015 15:00:40 GMT

To get the same day - related to their weekday-value - from a previous year you could simply subtract 364 from the date, maybe in a set analysis like this: sum({< DateNum = {"DateNum-364"}>} Value). Helpful could also be create a script-flag for this, like: DateNum - 364 as SameDayPreviousYear.

Also for movable holidays is a script-solution within a master-calendar recommended: Master Calendar with movable holidays. Many more examples and informations about master-calendar and date-fields could you find here: How to use - Master-Calendar and Date-Values.

- Marcus