topic Re: Rolling weeks backwards excluding holiday weeks in QlikView

Rolling weeks backwards excluding holiday weeks

klaus_kapero — Tue, 09 Feb 2016 16:42:41 GMT

Hello experts,

for production planning, we use the material consumption of the past 12 weeks. We calculate the average

consumption per week and the range of coverage.

Excluding holiday weeks! No material consumption took place two weeks at Christmas (week 52 and week 53 /

2015, German Calendar).

These two weeks of Holiday (red) have to be eliminated from the calculation of rolling 12 consumption.
The both past weeks "minus 13" and " minus 14" (blue) must be added to get a 12 weeks average.
This rule is valid until the start of the period of 12 weeks is 04.01.2016. Actual week in this example is week 6/2016.

The beginning and the end of the period of 12 weeks I have calculated with variables:

Script / Variables definition:
// Week -1 from today :
LET vD_WeekToday_Start_01 = Date(WeekStart( Today())-7, 'DD.MM.YYYY');
LET vD_WeekToday_End_01 = Date(WeekEnd( Today())-7, 'DD.MM.YYYY');

// Week -12 from today:
LET vD_WeekToday_Start_12 = Date(WeekStart( Today())-84, 'DD.MM.YYYY');
LET vD_WeekToday_End_12 = Date(WeekEnd( Today())-84, 'DD.MM.YYYY');

The Elimination of the Holiday weeks is (too) hard coded:
//Set analysis formula (Verbrauch = material consumption, Datum_KW_Mo = Monday date of week) :
sum({<Datum_KW_Mo_Verbrauch={'>=$(vD_WeekToday_Start_12)<=$(vD_WeekToday_End_01)'}>}Verbrauch_G)
-
sum({<Datum_KW_Mo_Verbrauch={'>=21.12.2015<=27.12.2015'}>}Verbrauch_G)
-
sum({<Datum_KW_Mo_Verbrauch={'>=28.12.2015<=03.01.2016'}>}Verbrauch_G)

Missing here is the addition of the weeks-13 and -14. For this purpose, conditions are necessary, for example:

//Rule1:
if(Date(WeekStart( Today())-84, 'DD.MM.YYYY') <= '21.12.2015','Substact week 52, substract week 53 and

Add Minus_week13, Add Minus_week14','Nested Rule2')

//Nested Rule2:
if(Date(WeekStart( Today())-84, 'DD.MM.YYYY') <= '28.12.2015','Substact week 53 and Add

Minus_week13','Nested Rule3')

//Nested Rule3 (12 weeks are outside the Christmas period)
if(Date(WeekStart( Today())-84, 'DD.MM.YYYY') <= '04.01.2016','Substact nothing Add nothing, build normal 12 weeks avg','ok')

I have found many examples of rolling periods but none with exception weeks.
Before I start to work with nested conditions and hard coding dates I want to ask the experts for a better solution..
Which approach is recommended ?

Kind regards

Klaus-Peter

Re: Rolling weeks backwards excluding holiday weeks

maxgro — Tue, 09 Feb 2016 21:39:25 GMT

This is just an idea; I think you can add a serial week field to the calendar; you can calculate the last 12 weeks as week today - 12; you'll get 12 or 14 weeks because week 51, 52 and 53 have the same week serial

............

2015_50 n-1

2015_51 n

2015_52 n

2015_53 n

2016_01 n+1

2016_02 n+2

............

Re: Rolling weeks backwards excluding holiday weeks

klaus_kapero — Wed, 10 Feb 2016 13:01:47 GMT

Additional Information:

Now I have build the nested if, it works, but it is hard coded:

if(Date(WeekStart( Today())-84, 'DD.MM.YYYY') <= '21.12.2015', // Substract week 52, substract week 53, Add Minus_week13, Add Minus_week14
(
sum({<Datum_KW_Mo_Verbrauch={'>=$(vD_WeekToday_Start_12)<=$(vD_WeekToday_End_01)'}>}Verbrauch_G)
-
sum({<Datum_KW_Mo_Verbrauch={'>=21.12.2015<=27.12.2015'}>}Verbrauch_G) // minus week 52
-
sum({<Datum_KW_Mo_Verbrauch={'>=28.12.2015<=03.01.2016'}>}Verbrauch_G) // minus week 53
+
sum({<Datum_KW_Mo_Verbrauch={'>=$(vD_WeekToday_Start_13)<=$(vD_WeekToday_End_13)'}>}Verbrauch_G) // plus week -13
+
sum({<Datum_KW_Mo_Verbrauch={'>=$(vD_WeekToday_Start_14)<=$(vD_WeekToday_End_14)'}>}Verbrauch_G) // plus week -14
),

// --------------------------------------------------------------------------------------------------------------------------------------------
if(Date(WeekStart( Today())-84, 'DD.MM.YYYY') <= '28.12.2015', // Substract week 53, Add Minus_week13
(
sum({<Datum_KW_Mo_Verbrauch={'>=$(vD_WeekToday_Start_12)<=$(vD_WeekToday_End_01)'}>}Verbrauch_G)
-
sum({<Datum_KW_Mo_Verbrauch={'>=28.12.2015<=03.01.2016'}>}Verbrauch_G) // minus week 53
+
sum({<Datum_KW_Mo_Verbrauch={'>=$(vD_WeekToday_Start_13)<=$(vD_WeekToday_End_13)'}>}Verbrauch_G) // plus week -13
),

// --------------------------------------------------------------------------------------------------------------------------------------------
if(Date(WeekStart( Today())-84, 'DD.MM.YYYY') <= '04.01.2016', // Substract nothing, Add nothing, build normal 12 weeks avg
(
sum({<Datum_KW_Mo_Verbrauch={'>=$(vD_WeekToday_Start_12)<=$(vD_WeekToday_End_01)'}>}Verbrauch_G)
),
'ok')))

Klaus-Peter

Re: Rolling weeks backwards excluding holiday weeks

marcus_sommer — Fri, 12 Feb 2016 09:43:56 GMT

I would use a master-calendar which considered the working-days and the holidays with a flag for them and a customized week-counter (maybe excluding weeks with less then four workingdays or whatever you need) so that I could use something like this:

sum({< CustomizedWeekCounter = {">=$(=max(CustomizedWeekCounter) - 12)"}>} Verbrauch_G)

How to get such a master-calendar, see: Master Calendar with movable holidays.

- Marcus

Re: Rolling weeks backwards excluding holiday weeks

klaus_kapero — Fri, 12 Feb 2016 11:34:26 GMT

Thank you Marcus for the quick reply,

the 'Master Calendar with movable Holidays' is one of the best calendars, I have ever seen for german issues.

I will link it to my main application and try to build the Holiday weeks.

Best regards so far.

Klaus-Peter