8 Replies Latest reply: Mar 9, 2011 10:28 AM by Brian Murphy RSS

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





        • Add month, add week functions
          Miguel Angel Baeyens de Arce

          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.

            • Add month, add week functions

              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

                • Add month, add week functions
                  Miguel Angel Baeyens de Arce

                  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.

                    • Add month, add week functions

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

                         

                         

                         

                        • Add month, add week functions
                          Miguel Angel Baeyens de Arce

                          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.

                            • Add month, add week functions

                              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!

                                • Add month, add week functions
                                  Miguel Angel Baeyens de Arce

                                  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.

                                    • Add month, add week functions

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