Author |
Message |
ridershaun
Newbie
Joined: 31 Aug 2010
Online Status: Offline
Posts: 32
|
Topic: Month/Year Parameter Posted: 28 Jan 2015 at 10:02am |
I have a report where the end user wants to enter the month and year in the report and then it will show data based on that month and year selected. The problem is I also need it to back 90 days because the report is averaging PO usage for the past 3 months. An example would be if you type in the month 12 and year 2014 you get usage for October, November, & December. The current formula I have is
month({PO_DATE})={?Month Number} and
Year({PO_DATE}) = {?Year}
If anyone has any way this could be accomplished it would be appreciated.
Thanks
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 28 Jan 2015 at 10:24am |
maybe this?
datediff('month',po_date,date({?year},{?month},1) in 0 to 2
|
IP Logged |
|
ridershaun
Newbie
Joined: 31 Aug 2010
Online Status: Offline
Posts: 32
|
Posted: 28 Jan 2015 at 10:44am |
I get a date range is required here error, highlighting the 0 to 2.
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 28 Jan 2015 at 10:53am |
did you use DATEDIFF or DATEADD
|
IP Logged |
|
Vimal Nair
Newbie
Joined: 29 Dec 2014
Location: United Arab Emirates
Online Status: Offline
Posts: 26
|
Posted: 28 Jan 2015 at 7:24pm |
You can do somewhat like below.
If Datepart('m',YourDate) In Datepart('m',Dateadd('q',-3,Date(({?year},{?month},01)))
to DatePart('m',(Date(({?year},{?month},01))) Then YourData
I Will explain the parts.
Datepart('m',Yourdate)
Will Give the month of your Data date .
Datepart('m',Dateadd('q',-3,Date(({?year},{?month},01)))
Will give the month of 3 months before the input date.
DatePart('m',(Date(({?year},{?month},01)))
Will Give the month of input date provided.
|
Born To Live
|
IP Logged |
|
ridershaun
Newbie
Joined: 31 Aug 2010
Online Status: Offline
Posts: 32
|
Posted: 30 Jan 2015 at 1:34am |
I used DATEDIFF
|
IP Logged |
|
ridershaun
Newbie
Joined: 31 Aug 2010
Online Status: Offline
Posts: 32
|
Posted: 30 Jan 2015 at 1:52am |
I used this formula, but it is only giving me data for the month that I enter and not the three months before. I have listed the formula below.
If Datepart('m',{PO_DATE}) In Datepart('m',Dateadd('q',-3,Date({?Year},{?Month Number},01)))
to DatePart('m',(Date({?Year},{?Month Number},01)))
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 30 Jan 2015 at 1:52am |
Sorry, looks like I forgot the closing parenth on the date function
datediff('month',po_date,date({?year},{?month},1)) in 0 to 2
|
IP Logged |
|
ridershaun
Newbie
Joined: 31 Aug 2010
Online Status: Offline
Posts: 32
|
Posted: 30 Jan 2015 at 2:42am |
Thank You
Edited by ridershaun - 30 Jan 2015 at 2:49am
|
IP Logged |
|
MansoorBari
Newbie
Joined: 02 Feb 2015
Location: Pakistan
Online Status: Offline
Posts: 1
|
Posted: 03 Feb 2015 at 11:00pm |
Year - Month and Day Formula here:
https://www.youtube.com/watch?v=OXthBuoGezg
|
Mansoor Bari
|
IP Logged |
|
|