Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Thanks for your reply. I can get the week number but how do I generate last 10 weeks?
I would need a control statement ?
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
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
You could put a select inside a loop, $i=0 to $=9
change Let $start=Week(Today())-$i.
May be something like this
week( date(today()- (num(weekday(Today()))+1)))-35