| Author |
Message |
vinpa
Newbie
Joined: 27 Jul 2009
Location: Canada
Online Status: Offline
Posts: 10
|
   Topic: Using 2 date ranges to filter different columns.
Posted: 27 Jul 2009 at 1:10pm |
Here is my issue:
I have a report that, when run, prompts for a date range (min/max) and sums the required data.
What I wish to do is subtract 1 year from that date in order to populate two separate columns (# products sold and total sales $) which would allow me to see sales from the same time frame a year earlier and compare the values.
I am new to Crystal Reports, but have above average SQL knowledge.
Using Crystal Reports 2008 (Version 12.0.0.683).
Thanks in advance!
|
 IP Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 27 Jul 2009 at 1:37pm |
gathering the data is easy, comparing side by side not as easy.
in your select statement just add an OR statement using Dateadd to get your data
({Table.datefield} in {?Start Date} to {?End Date})
or
({Table.datefield} in dateadd("y",-1,{?Start Date}) to dateadd("y",-1,{?End Date}) )
Not sure how you want to present it but this should get you started.
|
|
IP Logged |
|
vinpa
Newbie
Joined: 27 Jul 2009
Location: Canada
Online Status: Offline
Posts: 10
|
Posted: 28 Jul 2009 at 5:19am |
I'm sorry, which SELECT statement may you be referring to?
I currently have a report with "Commodity Code", "Part Number", "Part Description", "Ship Quantity", "Prior Year Ship Quantity", "Total Sales $", and "Prior Year Sales $". Of course, it's the "Prior Year..." columns that I would like to manipulate in order to get the previous year's stats.
The Select Expert shows:
not ({PART.PRODUCT_CODE} in ["Advertising", "Misc", "Packaging", "Repairs"]) and
{SHIPPER.SHIPPED_DATE} = {?Shipped Date} and
{CUSTOMER_ORDER.STATUS} = "C" and
{SHIPPER_LINE.USER_SHIPPED_QTY} <> 0.00
Not sure where to add your code, but when added to this I get a "This field name is not known."
Thanks for the quick reply, but I require a bit more mentoring to finish this report.  |
|
IP Logged |
|
vinpa
Newbie
Joined: 27 Jul 2009
Location: Canada
Online Status: Offline
Posts: 10
|
Posted: 28 Jul 2009 at 5:23am |
Of course I changed "Table.Datefield" to "SHIPPER.SHIPPED_DATE", and still field is unknown. 
Thanks.
|
|
IP Logged |
|
vinpa
Newbie
Joined: 27 Jul 2009
Location: Canada
Online Status: Offline
Posts: 10
|
Posted: 28 Jul 2009 at 5:51am |
Okay, I got that bit of code to work, so I now have:
{SHIPPER.SHIPPED_DATE} in Minimum ({?Shipped Date}) to Maximum ({?Shipped Date}) or
{SHIPPER.SHIPPED_DATE} in (DateAdd("y",-1,Minimum ({?Shipped Date}))) to (DateAdd("y",-1,Maximum ({?Shipped Date}))) and
not ({PART.PRODUCT_CODE} in ["Advertising", "Misc", "Packaging", "Repairs"]) and
{CUSTOMER_ORDER.STATUS} = "C" and
{SHIPPER_LINE.USER_SHIPPED_QTY} <> 0.00
This is giving me boolean values, all of which are "true". Still quite confused!
I wouldn't think this to be a difficult task, as comparing data year to year on a single report makes a lot of sense, sales-wise.
Is there no simple way to do this? Any help is greatly appreciated!
Thanks folks.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 28 Jul 2009 at 6:14am |
Sorry for the confusion. I was giving you a way to limit the data being brought into thr report via the select expert (select statement) so that you would only grab 2 comparible date ranges of data and exclude everything else. It sounds like you are placing this formula in a formula field which will return a True or a False. Also note you need to add parenthesis around the OR statement at the beginning of the statement.
( {SHIPPER.SHIPPED_DATE} in Minimum ({?Shipped Date}) to Maximum ({?Shipped Date}) or
{SHIPPER.SHIPPED_DATE} in (DateAdd("y",-1,Minimum ({?Shipped Date}))) to (DateAdd("y",-1,Maximum ({?Shipped Date}))) ) and
not ({PART.PRODUCT_CODE} in ["Advertising", "Misc", "Packaging", "Repairs"]) and
{CUSTOMER_ORDER.STATUS} = "C" and
{SHIPPER_LINE.USER_SHIPPED_QTY} <> 0.00
Your description of trying to get the previous years data on the same row as the current year is a little different. Crsytal is designed primarily to analyze row level data, not converge rows of data into one. You have to handle that outside of crystal and let crystal do what it is good at. If you are not married to analysis in a column fomrat, rethink your design and use the row level grouping power of crystal to compare items that way...
or you may be able to use a crosstab or chart to show the data in a column process analysis.
|
|
IP Logged |
|
vinpa
Newbie
Joined: 27 Jul 2009
Location: Canada
Online Status: Offline
Posts: 10
|
Posted: 28 Jul 2009 at 6:27am |
Thanks so much for your help!
I am not married to any specific format  , I just wanted to be able to compare/contrast our records from year to year, on a single report for easy reading. Is this not possible then?
Is there a way to get 2 date range prompts; one that will populate certain columns, and another to populate columns that represent previous year's data?
This seems like an all-to-easy task that is made into an almost impossible one.
If not, what is the best route to take? My manager would appreciate this very much!
Please help! Two days trying to figure out this junk.
Thanks again, friend.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 28 Jul 2009 at 6:45am |
Sorry, you cannot use two date ranges to populate side by side columns. Unless you create a View or stored procedure to create a table that has this data in it.
You can feed to sets of dates to get Row by row data (that was my first post process). Crystal does everything in rows of data. The columns that appear are only columns in that row.
Try using a CrossTab to get what you want. I am totally guessing here as I have no idea what your row level data is and what you need to get results on so please keep in mind that I am just trying to give you a process that might work for you if you adjust it based on your data source and needed end product.
You will need to create 2 formulas to group Month and Year side by side.
Create a formula as called "Year":
totext({SHIPPER.SHIPPED_DATE},"yyyy")
Create a formula as called "MOnth":
MOnthname(month({SHIPPER.SHIPPED_DATE}))
Add a crosstab and In the Crosstab Expert add Columns of @MONTH and then @YEAR.
This will group all your data for month year side by side.
As for the rows and sumarized fields I do not know what you need so I am not sure what else to tell you.
Try playing around with this process to see if it will work for you.
|
|
IP Logged |
|
vinpa
Newbie
Joined: 27 Jul 2009
Location: Canada
Online Status: Offline
Posts: 10
|
Posted: 28 Jul 2009 at 6:54am |
Okay, I will re-read that post a few times to get the idea. Again, thanks for the quick reply.
What other information could I provide for more explanation?
Thanks.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 28 Jul 2009 at 6:57am |
|
Post sample row level data as it looks when imported into crystal (you can dummy the numbers if it is sensitive info) and then what you want the end result report to look like
|
|
IP Logged |
|
|
|
