Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
stevelord
Valued Contributor

Aggregate by weeks within an activity date range?

Hi All, I have a data table like this:

ActivityName   ActivityStartDate   ActivityEndDate

Steps                2/1/2016              2/28/2016

Exercise           4/13/2016            6/7/2016

(There are actually many of these activities broken out by activityid tied to various clients, but that aggregation should fall into place.)

I want to make a calendar out of it like this:

ActivityName    ActivityDate    ActivityWeek

Steps                2/1/2016          1

Steps                2/2/2016           1

Steps                2/3/2016            1

....

Exercise          4/11/2016            6

Exercise         4/12/2016              6

Exercise         4/13/2016              6

How to do this?  (I have some master calendar script for a single date range that I'm trying to make smarter now, but hoping for an assist if I get stuck. )

Edit: Added a sample qvw and excel worksheet.  Sheet1 of the excel worksheet is what I have.  The second tab shows the result I need.  Thanks!  (Stuff I mention below this point I expect I can work out myself once I have a calendar table.)

-----------------------------------------

Ultimately, I'm going to join UserActivityLogs onto this.  for instance join the steps log on stepsdate=activitydate and steps.activityname=calendar.activityname, join the exercise log on exercisedate=activitydate and exercise.activityname=calendar.activityname, etc.

Wave 2 will be a bunch of counting how many users participated or met the goals of X number of activities based on days each user logged within the activity date ranges.  I tried just writing if statements  on the front, but it's timing out on accesspoint, so need to aggregate in script and put simpler charts and tables on the front.

1 Solution

Accepted Solutions
Not applicable

Re: Aggregate by weeks within an activity date range?

Hey Steve,

Try attached

BR,

Kuba

8 Replies

Re: Aggregate by weeks within an activity date range?

Steve,

Can you please send that Application / Raw Data?

With out raw data we can't do.

Creating A Master Calendar

Life is so rich, and we need to respect to the life !!!
Not applicable

Re: Aggregate by weeks within an activity date range?

Hello Steve,

For sure you must start with a Master Calendar. Here is a good article about that: The Fastest Dynamic Calendar Script (Ever)‌. Personally, I use simplified, traditional version mentioned by stephen-x.redmond in THIS comment in above article. Check both and see which one suits you better

Secondly to split data ranges as in your above example, you will need to use IntervalMatch function. Great tutorial here: IntervalMatch

Finally I attach a sample qvw file with implementation of Master Calendar and usage of IntervalMatch function.

I do not understand how are you numbering weeks though. I simply use Week number as per calendar.

I hope it helps.

Final table looks like this:

Screen Shot 07-26-16 at 08.33 PM.PNG

BR,

Kuba

stevelord
Valued Contributor

Re: Aggregate by weeks within an activity date range?

Added attachments, thanks!  First tab of the excel worksheet is what I have and second tab is what I need help getting to.  Feel free to drop new script into the qvw and re-attach for me to try.  I have a named license myself and can adapt it to our actual data structure.

Not applicable

Re: Aggregate by weeks within an activity date range?

OK I spot you provided additional explanation when I was writing post. New version of file here:

Screenshot:

Screen Shot 07-26-16 at 08.54 PM.PNG

stevelord
Valued Contributor

Re: Aggregate by weeks within an activity date range?

Hi Jakub, this is extremely close - the data structure and dates column are great, but something's off in the activity week column.  On the second challenge it has five days for the first activity week and has a 2-day activity week at the end.  I think maybe because it's using actual calendar weeks as a reference instead of the challengestartdate.  Do you have an angle to make that activityweek column consider the challengestartdate as day 1 of week 1, then run 7-day increments from there?

I think you've gotten it far enough that I might just bang out the activity week column with if statements on the final data table in script.  They'd be the same if statements that were causing accesspoint to time out, but our server could handle them back in script.  If you have an angle more optimal than if statements though, I'd like that.  Thanks!

I will come back to give you correct answer credit and maybe helpful myself or you or whoever irons out the activityweek column.  That dates column got me to 90%.

Not applicable

Re: Aggregate by weeks within an activity date range?

Hey Steve,

Try attached

BR,

Kuba

Not applicable

Re: Aggregate by weeks within an activity date range?

Hey stevelord,

Was my last file helpful, or you still require assistance with this problem?

BR,

Kuba

stevelord
Valued Contributor

Re: Aggregate by weeks within an activity date range?

It's brilliant thank you!  The date range thing and rank ceil / 7 as week stuff.   Sorry for late reply, I've have days off this week, and my top top priorities are actually nprinting automation and three job applicants we're interviewing soon, so a little jammed.  I see your solution works and saved it on my desktop to use as a guide, but might be a couple weeks before I have time to implement it.

Anyway, you're set to be the next gysburt wasserstein I think.  Thanks again!

-Steve

Community Browser