Rolling 5 days (working days)

[alexis]

Hi

I have an application where I need to monitor the behavior of "customers" for any changes in their purchasing patterns/volumes etc.

Talking with the project sponsor he felt that a rolling 5-day solution may offer us some indication. However he has explicitly specified that (for business reasons) we need to look at working days only (Monday-Friday in it's simplest form) - Any ideas on how I can develop such a solution.

Thanks in advance

Alexis

[alexis]

Thanks Gysbert - really interesting approach and well written article by Henric.

Unfortunately the calculated "Diff" variables (Month, Year etc) rely purely on mathematical differences; in my case I cannot do that as I need to take into consideration the "holes" in my calendar that represent non-working days.

Will an approach using the "Above" command perhaps offer a solution?

[prieper]

I would create a kind of rolling workday within the calendar, e.g.:

Dates:

    LOAD

        DATE(DATE#('20150101', 'YYYYMMDD') + RECNO()-1)    AS MyDate

    AUTOGENERATE

        500;

Calendar:

        LOAD

            *,

            NUMSUM(NETWORKDAYS(PEEK(MyDate), MyDate), PEEK(RunningWorkdays))    AS RunningWorkdays

        RESIDENT

            Dates      

        ORDER BY

            MyDate ASC;

        DROP TABLE Dates;

Formulas then to be adjusted

Edith wants to add that with with NETWORKDAYS-function you may also easily consider holidays and other days to be excluded.

HTH Peter

[Gysbert_Wassenaar]

You merely need to get a little creative. There are several options. If you only want to exclude weekends then you can generate a rolling 7 day AsOf table first and then remove the saturdays and sundays from that in a preceding load. That's the easiest solution. If you have other non-working days then you can first create a table that contains only the working days and add a numeric field that increases by one for each next working day. You can then use this numerical incrementing field to generate the AsOf table.

[#5 · Accepted solution]

Alexis,

I would make a data set without any weekend date and weekend date sales data and then check every 7 days to get this rolling number.

Worked on this last week but cannot make the set analysis work. With Sunny's help my sample qvw works.

Please see attached.

Hope this can help.

[alexis]

Sorry it tok so long to see this - wonderful solution and works a treat (changed your "7s" to "6s") to achieve rolling 5 days as your was rolling 6.

Thanks again

Alexis