Dynamic weekly change of measure using set analysis

[Anonymous]

Hi

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

[Gysbert_Wassenaar]

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

[Anonymous]

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

[Gysbert_Wassenaar]

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.

[#5 · Accepted solution]

try this:

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

[Anonymous]

That worked

Thank you!