topic Generating week numbers for the last 10 weeks. in QlikView

Generating week numbers for the last 10 weeks.

qliktech_uk — Mon, 28 Aug 2017 08:19:51 GMT

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

Re: Generating week numbers for the last 10 weeks.

eduardo_dimperio — Mon, 28 Aug 2017 19:29:50 GMT

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

Re: Generating week numbers for the last 10 weeks.

qliktech_uk — Mon, 28 Aug 2017 22:49:16 GMT

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

I would need a control statement ?

Re: Generating week numbers for the last 10 weeks.

eduardo_dimperio — Tue, 29 Aug 2017 12:45:39 GMT

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

Re: Generating week numbers for the last 10 weeks.

qliktech_uk — Tue, 29 Aug 2017 14:20:42 GMT

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

Re: Generating week numbers for the last 10 weeks.

eduardo_dimperio — Tue, 29 Aug 2017 14:42:16 GMT

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

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

Re: Generating week numbers for the last 10 weeks.

qliksus — Mon, 04 Sep 2017 04:02:35 GMT

May be something like this

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