topic Re: Rolling 5 days (working days) in QlikView

Rolling 5 days (working days)

alexis — Wed, 25 Nov 2015 10:29:50 GMT

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

Re: Rolling 5 days (working days)

settu_periasamy — Wed, 25 Nov 2015 12:52:33 GMT

Hi,

Look the below Linked Document. May be helps.

You can link with your date with the Working day Master Calendar.

Working Day Master Calendar

Re: Rolling 5 days (working days)

alexis — Wed, 25 Nov 2015 13:32:39 GMT

Hi Settu,

Thanks for replying. The question was not how to work out if it's a working day or not but more to do with "rolling" 5 working days - I am a little confused about how to code the "rolling" part.

Re: Rolling 5 days (working days)

settu_periasamy — Wed, 25 Nov 2015 14:01:24 GMT

fine. if i understood correctly, you can try to write a expression for rolling period, Like

Sum({<Date={‘>=$(=Date(Max(Date)-5))<=$(=Date(Max(Date)))’}>} Sales )

The Date is represent only the working days. So, you will get Latest 5 working day sales.

if you post with sample, it would be easier to help.

Set Analysis for Rolling Periods

Re: Rolling 5 days (working days)

alexis — Wed, 25 Nov 2015 14:08:45 GMT

Let's suppose your working dates (on 30/11/2015) are:

20/11

23/11

24/11

25/11

26/11

27/11

30/11

Your suggested:

Sum({<Date={‘>=$(=Date(Max(Date)-5))<=$(=Date(Max(Date)))’}>} Sales )

will collect results for the date range >=25/11 and <=30/11

This is not correct as the last 5 working dates would be >=24/11 and <=30/11

Re: Rolling 5 days (working days)

settu_periasamy — Wed, 25 Nov 2015 14:16:48 GMT

Yes, I didn't test that. I think this one will give Latest 5 days data..

=Sum({<Data={'>=$(=Date(Max(Date,5)))'}>}Sales)

Re: Rolling 5 days (working days)

marleygt — Wed, 25 Nov 2015 14:35:17 GMT

Hi alexis!

I am not so advance in QV, so just want to help a little I am able to.

I've developed in last project something for evaluate first 10 agents and customers; and I do with Variables.

So I thought could be applied to "days" too.

Take a look to the coarse sample herewith: maybe is not the a complete solution, but could help you to have a better working idea! (of what I've done).

Alex

Re: Rolling 5 days (working days)

Gysbert_Wassenaar — Wed, 25 Nov 2015 14:40:50 GMT

If you can work out which days are work days the you can use an AsOf table to link each workday with its previous four workdays. See this blog post for more information: The As-Of Table

Re: Rolling 5 days (working days)

alexis — Wed, 25 Nov 2015 14:55:25 GMT

Hi Alex

Thanks for the reply and example - I see what you have done in your example and unfortunately it's not quite what I am after - your example will only deal with "last n-days" where n is defined by your slider variable - mine is more of a "point-in-time" question. Thanks nevertheless.

Alexis

Re: Rolling 5 days (working days)

marleygt — Wed, 25 Nov 2015 15:01:58 GMT

Re: Rolling 5 days (working days)

alexis — Wed, 25 Nov 2015 15:06:28 GMT

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?

Re: Rolling 5 days (working days)

prieper — Wed, 25 Nov 2015 15:25:17 GMT

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

Re: Rolling 5 days (working days)

Gysbert_Wassenaar — Wed, 25 Nov 2015 16:01:00 GMT

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.

Re: Rolling 5 days (working days)

Mon, 30 Nov 2015 16:12:24 GMT

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.

Re: Rolling 5 days (working days)

alexis — Sat, 23 Jan 2016 08:58:50 GMT

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