topic Dynamic weekly change of measure using set analysis in QlikView

Dynamic weekly change of measure using set analysis

Anonymous — Thu, 18 Feb 2016 06:23:50 GMT

I would like to measure the weekly change in users.

here is an example of my data:

dt	users
1/1/2015	8000
1/2/2015	7000
1/3/2015	8500
1/4/2015	9000
1/5/2015	8800
1/6/2015	7100
1/7/2015	7500
1/8/2015	9000

Let's say I choose in the date picker 1/8/2015 I would like to get : 9000/8000-1

So My problem is trying to get the sum of a week back,

here is what I tried for the set analysis but can't get it to work:

sum( {$<dt= {GetFieldSelections(dt)-7}>} users)

I've tried many combinations, with $, ", ', + () - couldn't get it right. parsing to date/num didn't help either.

I tried using a variable:

let vSeven= "GetFieldSelections(dt)-7";

also no luck.

I am using Qlik Sense

Please don't send examples in qvw files

Your help is much appreciated

Re: Dynamic weekly change of measure using set analysis

Gysbert_Wassenaar — Thu, 18 Feb 2016 17:21:04 GMT

Fix it in the script:

Table1:

LOAD

dt,

users

FROM

...source...;

LEFT JOIN (Table1)

LOAD

Date(dt - 7) as dt,

users as users_from_seven_days_ago

RESIDENT

Table1;

The expression you can use will then be sum(users)/sum(users_from_seven_days_ago)-1

Re: Dynamic weekly change of measure using set analysis

Anonymous — Thu, 18 Feb 2016 17:40:42 GMT

Thanks, but I tried doing it in the script and it really slows down the process.

In addition I want to do this also for month, 2 days etc.

Therefore I am looking for a solution with set analysis

Re: Dynamic weekly change of measure using set analysis

Gysbert_Wassenaar — Thu, 18 Feb 2016 19:26:52 GMT

Ok, so imagine the user experience if the user has to wait for the calculations instead. If you think the non-userinteractive script is taking up time wait until the user has to wait for the userinteractive user interface to finish.

Re: Dynamic weekly change of measure using set analysis

Sun, 21 Feb 2016 11:50:27 GMT

try this:

sum( {$<dt=,[Date Diff]={'$(=Max([Date Diff])-7)'}>} users)

Re: Dynamic weekly change of measure using set analysis

Anonymous — Sun, 21 Feb 2016 14:26:08 GMT

That worked

Thank you!