topic Re: Rolling Period in QlikView

Rolling Period

kevbrown — Sat, 14 May 2016 20:50:54 GMT

I'm looking to create a bar chart of a count over the last 7 days by day, so for today, sunday to saturday, tomorrow will show Monday to Sunday. I'm sure this is easy but I can't get to grips with it. I have a date and week fields in my data.

Re: Rolling Period

swuehl — Sat, 14 May 2016 21:04:08 GMT

Try something like this:

Create a bar chart with dimension DateField and as expression something like this (assuming a date format like 'MM/DD/YYYY' for your DateField, adapt accordingly

=Sum({<DateField = {">=$(=Date(Today()-6,'MM/DD/YYYY'))<=$(=Date(Today(),'MM/DD/YYYY'))"}, WeekField=>} Value)

Re: Rolling Period

kevbrown — Sat, 14 May 2016 21:43:23 GMT

Thanks, thats great. Next question, I now want to add another aspect to the calculation. So as well as the following expressions - =Count(DISTINCT{<[Date] = {">=$(=Date(Today()-6,'DD/MM/YYYY'))<=$(=Date(Today(),'DD/MM/YYYY'))"}, [Financial Week]=>} [Issue ID]) - I want to look at only where the field Reporting Area = Dissatisfaction

Re: Rolling Period

swuehl — Sat, 14 May 2016 21:48:34 GMT

That should be pretty simple, too:

=Count(DISTINCT{<[Reporting Area]={'Dissatisfaction'}, Date] = {">=$(=Date(Today()-6,'DD/MM/YYYY'))<=$(=Date(Today(),'DD/MM/YYYY'))"}, [Financial Week]=>} [Issue ID])

A Primer on Set Analysis

Re: Rolling Period

kevbrown — Thu, 19 May 2016 07:21:37 GMT

Superb, worked a treat. Next question. Say i want a rolling 13 week. I do have week in my data so would i just swap week for date or even look use the current criteria but use 13 weeks worth of days, so, -91

Re: Rolling Period

swuehl — Thu, 19 May 2016 13:05:44 GMT

You mean, replace the -6 by -91?