Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

qliktech_uk
New 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

Tags (1)
6 Replies
eduardo_dimperi
Valued Contributor II

Re: Generating week numbers for the last 10 weeks.

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
New Contributor III

Re: Generating week numbers for the last 10 weeks.

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_dimperi
Valued Contributor II

Re: Generating week numbers for the last 10 weeks.

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
New Contributor III

Re: Generating week numbers for the last 10 weeks.

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_dimperi
Valued Contributor II

Re: Generating week numbers for the last 10 weeks.

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

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

qliksus
Valued Contributor

Re: Generating week numbers for the last 10 weeks.

May be something like this

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

Community Browser