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: 
hschultz
Partner - Creator
Partner - Creator

How can I change monthly start & end dates?

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.

1 Solution

Accepted Solutions
Not applicable

Hello,

          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.

Regards,

Daniele

View solution in original post

5 Replies
flipside
Partner - Specialist II
Partner - Specialist II

Hi,

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

load

     ENTERED_Date,

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

.....

flipside

Not applicable

Hello,

          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.

Regards,

Daniele

flipside
Partner - Specialist II
Partner - Specialist II

Yep, the calendar table is a good idea.

For the date offset calculation in this case, it isn't just a fixed day subtraction though - you need to take into account the varying month lengths otherwise the period start will vary 24th to 27th.

flipside

hschultz
Partner - Creator
Partner - Creator
Author

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.

hschultz
Partner - Creator
Partner - Creator
Author

*Will be able to say 100% working after i am on-site to reload QVW