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: 
qliktech_uk
Contributor III
Contributor III

Generating week numbers for the last 10 weeks.

Hi,

I have a query that is dependent on 3 variables, this is look at the sales thats happening in the last 35 days.

the query is

select

     *

from

     sales

where

     week_number = Previous week number

     and agent_sale_Date between variable1 (last saturday) and variable2(last saturday - 35)

I need to do the same going back 10 weeks!!

I would need to generate the week numbers from last week going back 10 weeks. I need to run this on a Monday, so the other 2 variables I can hard code or use an inline table I think. How do we generate the week numbers, and also is there a way to do variable 2 and 3 on the fly? many thanks for your help

variable 2  variable 3

2                     35

9                      42

6 Replies
eduardo_dimperio
Specialist II
Specialist II

Hi,

Try this:

Week(Today())-10 //replace today() if necessary


Read this article week - script and chart function ‒ Qlik Sense


Use the result to your query

qliktech_uk
Contributor III
Contributor III
Author

Thanks for your reply. I can get the week number but how do I generate last 10 weeks?

I would need a control statement ?

eduardo_dimperio
Specialist II
Specialist II

Did you want all data from 10 weeks to now?

If that case you just put the result in a variable like

Let $start=Week(Today())-10

Let $end=Today()


and use in your Select



select

     *

from

     sales

where

agent_sale_Date between $start and $end

qliktech_uk
Contributor III
Contributor III
Author

I need to show the data for each week. Each week needs to look at the date from last sunday going back 35 days.

So my result should be

Week  Sales in last 35 

10             100

9                   90

8                   130

eduardo_dimperio
Specialist II
Specialist II

You could put a select inside a loop, $i=0 to $=9

change Let $start=Week(Today())-$i.

qliksus
Specialist II
Specialist II

May be something like this

week( date(today()- (num(weekday(Today()))+1)))-35