Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Rolling Weeks Question

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)

          

RepFiscalYearMonthMonth-YearWeekNumWeek Beginning30DayPipe90DayPipeContactAddsFirstinsNewOps
RachelFY15AprApr-201514/6/15$441,600$797,600000
RachelFY15AprApr-201524/13/15$410,500$862,600000
RachelFY15AprApr-201534/20/15$410,500$862,600000
RachelFY15AprApr-201544/27/15$387,000$689,100051
RachelFY15MayMay-201555/4/15$318,600$659,100000
RachelFY15MayMay-201565/11/15$305,000$645,500001
RachelFY15MayMay-201575/18/15$267,000$599,500001
RachelFY15MayMay-201585/25/15$388,000$707,500052
RachelFY15JunJun-201596/1/15$314,300$840,550000
RachelFY15JunJun-2015106/8/15$314,300$905,050000
RachelFY15JunJun-2015116/15/15$314,300$895,050000
RachelFY15JunJun-2015126/22/15$322,300$986,050001
RachelFY15JulJul-2015136/29/15$321,300$978,050001
RachelFY15JulJul-2015147/6/15$257,800$995,050000
RachelFY15JulJul-2015157/13/15$282,800$1,020,0500101
RachelFY15JulJul-2015167/20/15$295,050$1,020,0500101
RachelFY15JulJul-2015177/27/15$360,050$1,028,0500101
RachelFY15AugAug-2015188/3/15$259,300$929,800000
RachelFY15AugAug-2015198/10/15$259,300$937,988000
RachelFY15AugAug-2015208/17/15$259,300$937,988000
RachelFY15AugAug-2015218/24/15$282,300$877,988000
RachelFY15AugAug-2015228/31/15$282,300$404,688000
RachelFY15SepSep-2015239/7/15$261,500$653,188200
RachelFY15SepSep-2015249/14/15$261,500$643,188400
RachelFY15SepSep-2015259/21/15$261,500$657,188342
RachelFY15SepSep-2015269/28/15$29,500$504,188533
RachelFY15OctOct-20152710/5/15$130,000$476,688040
RachelFY15OctOct-20152810/12/15
RachelFY15OctOct-20152910/19/15
RachelFY15OctOct-20153010/26/15
RachelFY15NovNov-20153111/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.

1 Solution

Accepted Solutions
MVP
MVP

Re: Rolling Weeks Question

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

6 Replies
MVP
MVP

Re: Rolling Weeks Question

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

Not applicable

Re: Rolling Weeks Question

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?

MVP
MVP

Re: Rolling Weeks Question

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

Not applicable

Re: Rolling Weeks Question

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;

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

MVP
MVP

Re: Rolling Weeks Question

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.

Not applicable

Re: Rolling Weeks Question

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