Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Dynamic weekly change of measure using set analysis

Hi

I would like to measure the weekly change in users.

here is an example of my data:

dtusers
1/1/20158000
1/2/20157000
1/3/20158500
1/4/20159000
1/5/20158800
1/6/20157100
1/7/20157500
1/8/20159000

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

1 Solution

Accepted Solutions
Not applicable
Author

try this:

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

View solution in original post

5 Replies
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


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author

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.


talk is cheap, supply exceeds demand
Not applicable
Author

try this:

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

Anonymous
Not applicable
Author

That worked

Thank you!