Add month, add week functions

[#1]

I'm looking for an alternative to the AddMonth Function which would allow me to be able to add weeks. Unfortunately there is no such thing as an "ADDWEEk" Function. At the moment I have something like below but unfortunately there is no such function. Anyone have any ideas?

=

date(addWeeks(Date_MonthYear,-4),'MMM-YYYY'))

[Miguel_Angel_Baeyens]

Hello,

Since a week is fixed to 7 days, you can add them within a Date() function:

Date(Today() + (7 * vNumberOfWeeks), 'MMM-YYYY')

Where vNumberOfWeeks is a variable (for example) you have previously created in the Settings menu, Variable Overview, and may be filled from an inputbox or a slider.

Hope that helps.

[#3]

Thanks for the reply and also apologies for the delay in getting back to you.

I'm now trying to have a function which calculates sales for the previous 3 weeks from a given time

Would it be ok to use this variable: " =week(today())-21 "

And how would you recommend implementing this variable into the function

[Miguel_Angel_Baeyens]

Hello,

I'm glad to help.

I'd do what you want first creating a variable vGivenDate where you set the date you want to get. Use that variable in a calendar, slider or input box object. Then using an expression like

Sum({< YourSalesDateField = {'$(=Date(vGivenDate - 21))'} >} Sales)

Note that QlikView is case sensitive to field names and values, so the date in variable vGivenDate and YourSalesDateField must be formatted alike.

Hope that helps.

[#5]

Thanks again for your help Miguel, I appreciate it, however I'm still having trouble implementing the correct variable.

i'm trying to calculate the the average sales within 2 parameters.

• Parameter 1: A selected date
• Parameter 2: Three weeks (or 21 days) prior to the selected date in parameter 1

Currently I'm having difficulty trying to create variables for these figures. I've tried the following but none of them seem to work. Thanks again for your help, hopefully you might be able to point me further in the right direction!

• =

  Date(Today()-21 , 'MMM-YYYY')

• =

  week(Date_WeekYear())

• =

  week((Date_WeekYear),'WW-YYYY')

• =

  week((Date_WeekYear,-3),'WW-YYYY')

  week((Date_WeekYear,-3),'WW-YYYY')

[Miguel_Angel_Baeyens]

Hi,

Use some variables. Let's say vSelectedDate that is the one the user is going to change. The second date is fixed (vSelectedDate -21 days).

So the expression should look like

Sum({< YourDateField = {'>=$(=Date(vSelectedDate -21))<=$(=Date(vSelectedDate))'} >} Amount)

The field "YourDateField" stores a date formatted as returned by Date(vSelectedDate).

Hope that helps.

[#7]

At the moment my main issue if with creating the appropriate variable. This is my expression...

if(count({$<Date_WeekYear={">=$(#vNewDate -21) <=$(#vNewDate)"}>} product)>min_stock_level,count({$<Date_WeekYear={">=$(#vNewDate) <=$(#vNewDate -21)"}>} product - min_stock_level, 'no'))Sum({< YourDateField = {'>=$(=Date(vSelectedDate -21))<=$(=Date(vSelectedDate))'} >} Amount)

My variable vNewDate = date(week(Date),'WW-YYYY')... However when I put this string in my label for the expression "Sales: '&vNewDate&'" the output on the column header on my pivot table is "Sales:" leaving out the variable... My question would be if you knew why this variable was returning a null value and whether you had any ideas as to a more effective variable i could use. Thanks again for your help and patience!

[Miguel_Angel_Baeyens · Accepted solution]

Hello Brian,

First of all, 'W', 'WW', 'WWW' as format in dates is not for Weeks but for Week Days. So the first thing you need to do is to change that format to something compatible with your main sales date field, and likely, there's no need to change anything elaborating the expression I posted above.

Hope that helps.

[#9]

Thanks Miguel, I was finally able to create the variables.

v1 - =date(Date_MonthYear),'MMM-YYYY')

v2 - =date(Date_MonthYear-21),'MMM-YYYY')

Using this expression I'm able to see the average between the two chosen dates. Thanks again for the assistance.

if(count({$<Date_MonthYear={">=$(#vNewDate) <=$(#vMinus3wks)"}>} product)>min_stock_level,
(count({$<Date_MonthYear={">=$(#vNewDate) <=$(#vMinus3wks)"}>} product) - min_stock_level), 'no')