Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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?
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
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;
//===================================
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.
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