Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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
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
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.
*Will be able to say 100% working after i am on-site to reload QVW