| Author |
Message |
Stircrazy08
Newbie
Joined: 12 Nov 2014
Location: United States
Online Status: Offline
Posts: 34
|
  
Topic: Date Range Help.
Posted: 19 Mar 2015 at 10:01am |
|
need help.
i have a parameter, which is a user entering the beginning and ending of the month the user wants the report to run for. {?DATERANGE}
from this date range, i need to get the following dates.
so if someone entered the range 2/1-2/31/2015
the year before last.
so i would need to get 01/01/2013 thru 02/28/2013
last year range
so i would need to get 01/01/2014 thru 02/28/2014
This year range
so i would need to get 01/01/2015 thru 02/28/2015
also would need
02/01/2015 thru 02/28/2015 (daterange)
and
02/01/2014 thru 02/28/2014 (daterange - 1year)
i dont want to hard code any dates, i need them calculated from the input daterange
was thinking of using dateadd to pull the year off, and subtract 2 but having troubles with how this would work.
thanks,
|
|
Peter F
|
 IP Logged |
|
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 19 Mar 2015 at 10:54am |
1. how is the user entering the param?
typing a string?
using one date?
or one date field with a range?
or two date fields?
Something else?
From this other than the range they are entering what else so you need? I don't understand your examples...is it two full years from first day of the month?
|
| IP Logged |
|
Stircrazy08
Newbie
Joined: 12 Nov 2014
Location: United States
Online Status: Offline
Posts: 34
|
Posted: 19 Mar 2015 at 11:16am |
|
currently i created a date/time parameter.
so when the user kicks the job off, there is a icon, with a calender box's to the right of both date fields, they can click that a calender pops up and they select the date and it populates the date field with date.
so you enter a date.
the report output would need following.
Feb 1 - 28 2015 - current month
Feb 1 - 28 2014 - last year same month
jan 1 - feb 28 2015 current year to date
jan 1 - feb 28 2014 same range for last year
jan 1 - feb 28 2013 same range for year before last
i am not sure if the date range parameter was/is the best way to do this.
Edited by Stircrazy08 - 19 Mar 2015 at 11:18am
|
|
Peter F
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 19 Mar 2015 at 11:56am |
|
can they pick more than one month or I should say should they be allowed to pick more than one month?
|
| IP Logged |
|
Stircrazy08
Newbie
Joined: 12 Nov 2014
Location: United States
Online Status: Offline
Posts: 34
|
Posted: 20 Mar 2015 at 3:06am |
|
they can only pick one month to compare, so if run today, it would be for feb month, and the YTD would be thru feb 28.
but if they pick january month, then month would be for january and the ytd would be thru jan 31 for the year columns.
was thinking is it possible for them just to enter one date, like feb28, 2015, instead of having them input a range, and then pick apart that date to get my other values like 01/01/2013 ?
|
|
Peter F
|
| IP Logged |
|
Stircrazy08
Newbie
Joined: 12 Nov 2014
Location: United States
Online Status: Offline
Posts: 34
|
Posted: 20 Mar 2015 at 6:40am |
|
i created a single date parameter. (?pronptfromdate)
and then i created a formula field. @selectfromdate
dateVar P2Date := {?promptfromdate};
P2DAte;
when i put this on my report canvas i get.
3/20/2015 12:00:00
I cannot just change the format of the field, because i want to use my @selectfromdate as part of my select qry.
would it be possible to get extract the year from this parameter field, so i can build a formula field to be 01/01/2013, this way i can use this date field as part of my qry select?
|
|
Peter F
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 20 Mar 2015 at 6:52am |
i think you need to seperate your thougt process here.
1 you need a select statment that will give you the full 2 years of data that need to be used to get your 4 comparable date ranges
there are a lot of ways to do this but one simple way is to use datediff and a month which should give you full months of data regardless of the month day selected in the param
datediff("m",table.datefield,?pronptfromdate) in 0 to 24
next you can deal with the values in each of your 4 buckets by either using running totals with evaluation formula
or
shared variable formula fields that replicate that
or
formula to set the conditionally set values to 0 and sum these
|
| IP Logged |
|
Stircrazy08
Newbie
Joined: 12 Nov 2014
Location: United States
Online Status: Offline
Posts: 34
|
Posted: 20 Mar 2015 at 8:08am |
|
DB - the datediff worked like a charm!!!
the other part with my issue is my "Buckets" that i am using, i currently have the dates hard coded for the range that i want, can you advise me how i can change this so that i dont have to keep going into this report to change these dates.
Here are my "Buckets"
each is a seperate formula field.
if {AR_InvoiceHistoryHeader.InvoiceDate} in Date (2015, 02, 01) to Date (2015, 02, 28)then "CMON"
---
if {AR_InvoiceHistoryHeader.InvoiceDate} in Date (2014, 02, 01) to Date (2014, 02, 28)then "PMON"
---
if {AR_InvoiceHistoryHeader.InvoiceDate} in Date (2015, 01, 01) to Date (2015, 02, 28)then "CYEAR"
---
if {AR_InvoiceHistoryHeader.InvoiceDate} in Date (2014, 01, 01) to Date (2014, 02, 28)then "PYEAR"
---
if {AR_InvoiceHistoryHeader.InvoiceDate} in Date (2013, 01, 01) to Date (2013, 02, 28)then "2PYEAR"
Edited by Stircrazy08 - 20 Mar 2015 at 8:12am
|
|
Peter F
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 20 Mar 2015 at 8:21am |
you have to be careful as you only have one row of data that can be in more than one 'bucket'. I assume you are trying to do a sales sum or the like so I would recommend using 4 formula fields (that you can sum). something like the below.
You may need to tweak them.
If you place all 4 on the detail section you can 'debug' them to make sure they are setting each row for each bucket to value or 0 the way you expect them to.
// "CMON"
if datediff("m",{AR_InvoiceHistoryHeader.InvoiceDate} ,?pronptfromdate)=0 Then table.amount else 0
// "PMON"
if datediff("m",{AR_InvoiceHistoryHeader.InvoiceDate} ,?pronptfromdate)=13 Then table.amount else 0
// "PYEAR"
datediff("m",{AR_InvoiceHistoryHeader.InvoiceDate} ,?pronptfromdate) in 0 to 12 Then table.amount else 0
// "2PYEAR"
datediff("m",{AR_InvoiceHistoryHeader.InvoiceDate} ,?pronptfromdate) in 13 to 24 Then table.amount else 0
|
| IP Logged |
|
Stircrazy08
Newbie
Joined: 12 Nov 2014
Location: United States
Online Status: Offline
Posts: 34
|
Posted: 20 Mar 2015 at 8:32am |
|
the previous years thru date is only thru the month selected, i edited my previous post.
not sure your previous response would work because i need to start at the beginning of the year. 01/01/2013, and that changes after every month, right now it would be 26months i believe. so in dec 2015 when we run the report, it would give us 36months of data.
|
|
Peter F
|
| IP Logged |
|
|
|
