Rolling Weeks Question

[#1]

I am trying to create a KPI that Sums the last four weeks of data.  I have read and tried the numerous solutions on creating Rolling 'N' expressions in the posts to no joy. I suspect either my data is formatted wrong or I am missing something. I am fairly new to Qlik.

The application is to show three activity metrics for salespeople as a rolling four week total (thus a month rolling weekly). below a snapshot of my data (in excel)

Rep	FiscalYear	Month	Month-Year	WeekNum	Week Beginning	30DayPipe	90DayPipe	ContactAdds	Firstins	NewOps
Rachel	FY15	Apr	Apr-2015	1	4/6/15	$441,600	$797,600	0	0	0
Rachel	FY15	Apr	Apr-2015	2	4/13/15	$410,500	$862,600	0	0	0
Rachel	FY15	Apr	Apr-2015	3	4/20/15	$410,500	$862,600	0	0	0
Rachel	FY15	Apr	Apr-2015	4	4/27/15	$387,000	$689,100	0	5	1
Rachel	FY15	May	May-2015	5	5/4/15	$318,600	$659,100	0	0	0
Rachel	FY15	May	May-2015	6	5/11/15	$305,000	$645,500	0	0	1
Rachel	FY15	May	May-2015	7	5/18/15	$267,000	$599,500	0	0	1
Rachel	FY15	May	May-2015	8	5/25/15	$388,000	$707,500	0	5	2
Rachel	FY15	Jun	Jun-2015	9	6/1/15	$314,300	$840,550	0	0	0
Rachel	FY15	Jun	Jun-2015	10	6/8/15	$314,300	$905,050	0	0	0
Rachel	FY15	Jun	Jun-2015	11	6/15/15	$314,300	$895,050	0	0	0
Rachel	FY15	Jun	Jun-2015	12	6/22/15	$322,300	$986,050	0	0	1
Rachel	FY15	Jul	Jul-2015	13	6/29/15	$321,300	$978,050	0	0	1
Rachel	FY15	Jul	Jul-2015	14	7/6/15	$257,800	$995,050	0	0	0
Rachel	FY15	Jul	Jul-2015	15	7/13/15	$282,800	$1,020,050	0	10	1
Rachel	FY15	Jul	Jul-2015	16	7/20/15	$295,050	$1,020,050	0	10	1
Rachel	FY15	Jul	Jul-2015	17	7/27/15	$360,050	$1,028,050	0	10	1
Rachel	FY15	Aug	Aug-2015	18	8/3/15	$259,300	$929,800	0	0	0
Rachel	FY15	Aug	Aug-2015	19	8/10/15	$259,300	$937,988	0	0	0
Rachel	FY15	Aug	Aug-2015	20	8/17/15	$259,300	$937,988	0	0	0
Rachel	FY15	Aug	Aug-2015	21	8/24/15	$282,300	$877,988	0	0	0
Rachel	FY15	Aug	Aug-2015	22	8/31/15	$282,300	$404,688	0	0	0
Rachel	FY15	Sep	Sep-2015	23	9/7/15	$261,500	$653,188	2	0	0
Rachel	FY15	Sep	Sep-2015	24	9/14/15	$261,500	$643,188	4	0	0
Rachel	FY15	Sep	Sep-2015	25	9/21/15	$261,500	$657,188	3	4	2
Rachel	FY15	Sep	Sep-2015	26	9/28/15	$29,500	$504,188	5	3	3
Rachel	FY15	Oct	Oct-2015	27	10/5/15	$130,000	$476,688	0	4	0
Rachel	FY15	Oct	Oct-2015	28	10/12/15
Rachel	FY15	Oct	Oct-2015	29	10/19/15
Rachel	FY15	Oct	Oct-2015	30	10/26/15
Rachel	FY15	Nov	Nov-2015	31	11/2/15

So, for example, the KPI for "FirstIn's" should result in 11 and "New Opps" should result in 5. There is one tab per Sales Rep. All the simple Sums and Averages seem to be working fine, I just can't get the rollings to work.

I have tried rangesum(Above(Sum(Firstins),0,4)) to no avail. The result I get is 51- which is the sum of the entire column.

Any help is much appreciated. I have tried to figure this out myself for too long.

[Oleg_Troyansky]

The solution based on the Above() function, even if you manage to get it to work, will only be relevant in a table where data is listed at the detailed level, weekly. Imagine that you need the same kind of information in a free-standing KPI calculation, as a total (or average) of all sales people.

A more universal solution is to use the so called "As of Date" table. There are multiple references on this site that explain how to build and use the "As of Date" table. You can also find it in my blog article here:

QlikView Blog Q-Tip #4 How to Use "As of Date" table | Natural Synergies

I also describe it in more detail in my new book QlikView Your Business (it also applies to Qlik Sense).

cheers,

Oleg Troyansky

QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense

[#3]

Thanks for the quick reply Oleg.

In reviewing your solution on the blog, it makes sense and may be the solution to my issue. Can you quickly apply it to my issue of rolling weeks? In your load script, can I just substitute Week/s for the references to Month/s?

Also a real newbie question- just to check, I add the script to the data loader, right?

[Oleg_Troyansky · Accepted solution]

Hi Steve,

sorry for the delay - our time difference doesn't help 🙂

With weeks, the math is simpler. While AddMonths() helps us overcome the different lengths of different months, weeks are conveniently sized for us at 7 days each. So, the condition for the current week would be:

If( Date1> Date2-7, 1, 0) as _ThisWeek_Flag.

Or, if you'd like to stick to the same Week Start date, you could do something like this:

If( WeekStart(Date1) = WeekStart(Date2), 1, 0) as _ThisWeek_Flag

Or, to calculate the same for the "Last 12 weeks" condition:

If( WeekStart(Date1) >= WeekStart(Date2 - 12*7), 1, 0) as _Last12Weeks_Flag

And - yes, this logic belongs in the Data Load Script. We typically add it when we create the Master Calendar table. If you don't have one, simply add it to the load statement where your Date field is loaded.

cheers,

Oleg Troyansky

[#5]

Oleg- Sorry for taking so long to get back. My projects shifted for a few days. It appears that your solution is exactly what I needed in the first place. I can't seem to get it to work though. I believe its a newbie thing as my script errors out at the "Date1". Are the 'Date1' and 'Date2' fields supposed to be a placeholder for something else?

My calendar script is below:

//Generate calendar range based on the Claendar start and end variables. All date within the range are genrated.

LET vCalendarStart = Date#('2015-01-01'); // Static start

LET vCalendarEnd = YearEnd(today()); // End of current year

LET vCalendarLength = Num(Date#(vCalendarEnd)) - Num(Date#(vCalendarStart)) + 1;

Calendar:

LOAD

  *,

  If(Date>=(Today()-30) AND Date<Today(), 1, 0) AS Is30DayPeriod,

  If(Week=Week(Today()), 1, 0) AS IsCurrentWeek,

  If(Date<=YearEnd(today()) AND Date>Today(), 1, 0) AS IsLeftCurrentYear

;

LOAD

  *,

  Num(Ceil(Month/3), 'Q0') AS Quarter,

  WeekDay(Date) AS WeekDay,

  Date(MonthStart(Date), 'YYYY MMM') AS YearMonth,

  WeekName(Date) AS YearWeek,

  Date#(Date(Date, 'MMMM'), 'MMMM') AS MonthName //Month according to MonthLongNames definition

;

LOAD

  Date,

  Year(Date) AS Year,

  Num(Month(Date), '00') AS Month,

  Num(Day(Date), '00') AS Day,

  Num(Week(Date), '00') AS Week

  ;

LOAD

  Date($(#vCalendarStart) + RecNo()-1) AS Date 

AutoGenerate $(#vCalendarLength);

// 1. Create a distinct list of Months:

TransactionMonths:

Load distinct

  Month

Resident

  Calendar

;

// 2. Create a distinct list of DisplayMonths:

DisplayMonths:

Load

  Month as DisplayMonth

Resident

  TransactionMonths

;

// 3. Create a list of all possible combinations:

join (TransactionMonths) load * resident DisplayMonths;

// 4. Reload the same table and calculate all the necessary flags:

MonthsLink:

Load

Month,

DisplayMonth,

IF( Month >= AddMonths(DisplayMonth, -6) , 1, 0) as Last6MonthsFlag,

IF( Month >= AddMonths(DisplayMonth, -12) , 1, 0) as Last12MonthsFlag,

IF( Month = DisplayMonth , 1, 0) as SameMonthFlag

Resident

TransactionMonths

;

drop table TransactionMonths;

//===================================

// 1. Create a distinct list of Weeks:

TransactionWeeks:

Load distinct

Week

Resident

Calendar

;

// 2. Create a distinct list of DisplayWeeks:

DisplayWeeks:

Load

Week as DisplayWeeks

Resident

TransactionWeeks

;

// 3. Create a list of all possible combinations:

join (TransactionWeeks) load * resident DisplayWeeks;

// 4. Reload the same table and calculate all the necessary flags:

WeeksLink:

Load

  Week,

  DisplayWeeks,

  If( WeekStart(Date1) = WeekStart(Date2), 1, 0) as _ThisWeek_Flag,

  If( WeekStart(Date1) >= WeekStart(Date2 - 12*7), 1, 0) as _Last12Weeks_Flag

Resident

  TransactionWeeks

;

drop table TransactionWeeks;

//===================================

[Oleg_Troyansky]

Hi Steve,

yes, Date1 and Date2 were placeholders for the transaction date and the display date. I just used them to give you an example of the logic.

[#7]

Hi Steve,

How did you and Oleg fixed this? I tried copying your script and adding my own performancedate but it says it can't find a date.:

// 4. Reload the same table and calculate all the necessary flags:

WeeksLink:

Load

  Week,

  DisplayWeeks,

//  If( WeekStart(Date) = WeekStart(Date), 1, 0) as _ThisWeek_Flag,

  If( WeekStart(Date) >= WeekStart(Date - 12*7), 1, 0) as _Last12Weeks_Flag

Resident

  TransactionWeeks