5 Replies Latest reply: Mar 28, 2012 5:39 AM by Hein Schultz RSS

    How can I change monthly start & end dates?

    Hein Schultz

      Hello everyone

      I am very new to QlikView, and this is my first post, hoping someone can assist me.


      We are trying to change QlikView's deffinition of a Month to run from the 25th of a month to the 24th of a month.

      So when we select March, it runs from 25Feb, to 24Mar.


      I have been able to set a fiscal year using this code:

      Year(AddMonths(ENTERED_Date,6)) AS YEAR,


      Although it should subtract 5days, i am looking for a starting point but there is now function like AddDays.


      Or am i trying a completely wrong avenue?


      Any assistance will be greatly appreciated thank you.

        • How can I change monthly start & end dates?
          Dave Riley



          You could just load the dates as normal then create a list box to select your working/fiscal period with the following expression ...


          =Month(ENTERED_Date + (Day(MonthEnd(ENTERED_Date))-24))


          ... or you can use the same code to create a new field in the load script ...




               Month(ENTERED_Date+ (Day(MonthEnd(ENTERED_Date))-24))  as NewDate




          • How can I change monthly start & end dates?


                      I faced this issue before when the customer asked me for a customised fiscal calendar.

            If you simply want to subtract five days use daystart( date, -5 ). Check the QV Reference Manual for further details.

            My suggestion is to create a calendar table in your database. The table will have all the days of the year plus customised columns with fiscal week name, fiscal month name, fiscal quarter name etc. This could sound difficult to implement but is easier to maintain and to update in according to further customer requests.




            • How can I change monthly start & end dates?
              Hein Schultz

              Hi there.

              Thank you both for your answeres.

              I have tried this:


              =Month(ENTERED_Date + (Day(MonthEnd(ENTERED_Date))-24))


              But i changed it to 25.

              It seems to be working great if i compare on a straight chart. Every month is correct. Thank you flipside.

              Will now put it in the load and attempt to use as a selection field, as the expression for selection is not workoing.