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: 
Not applicable

Add month, add week functions

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'))





1 Solution

Accepted Solutions
Miguel_Angel_Baeyens

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.

View solution in original post

8 Replies
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.

Not applicable
Author

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.

Not applicable
Author

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.

Not applicable
Author

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

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.

Not applicable
Author

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')