| Author |
Message |
CMEPCMEP
Newbie
Joined: 26 Apr 2010
Online Status: Offline
Posts: 15
|
  
Topic: Data Two Years Ago
Posted: 26 Apr 2010 at 12:46pm |
|
I'm working in an Inventory Management module (Mas90). I need issues and sales for YTD, prior year and two years back. I, of course, have the YTD and prior, but I see no database for two years ago (or earlier). How can I grab that data, or can I? Any input greatly appreciated.
|
 IP Logged |
|
|
|
delstar
Newbie
Joined: 11 Jun 2009
Online Status: Offline
Posts: 26
|
Posted: 27 Apr 2010 at 2:41am |
|
I had a similar problem not too long ago. I found a handy technique using the dateserial function :
{@Date} in dateserial(year(currentdate)-2,month(1),day(1)) to dateserial(year(currentdate)-2,month(currentdate),day(currentdate))
|
| IP Logged |
|
CMEPCMEP
Newbie
Joined: 26 Apr 2010
Online Status: Offline
Posts: 15
|
Posted: 27 Apr 2010 at 4:02am |
I'm too new at this to understand where you're at. Could you break this down or give me more details.
Additionally, I'm still wondering if I'm at the mercy of the databases. This is super basic accounting. 95% of all the examples I see in help and in my books are text oriented predominantly used for marketing. Where's the accounting, numbers and time periods, the other half?! Frustrating. Edited by CMEPCMEP - 27 Apr 2010 at 4:02am
|
| IP Logged |
|
CMEPCMEP
Newbie
Joined: 26 Apr 2010
Online Status: Offline
Posts: 15
|
Posted: 28 Apr 2010 at 5:00am |
Originally posted by delstar
I had a similar problem not too long ago. I found a handy technique using the dateserial function :
{@Date} in dateserial(year(currentdate)-2,month(1),day(1)) to dateserial(year(currentdate)-2,month(currentdate),day(currentdate))
Can anyone else give me a little more insight here. I don't know where this is at or where I'm putting it. Is it part of my formula? I can't find anything on dateserial.
I'm working with a very old vesion of Crystal and Mas90 by the way (if that matters).
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 28 Apr 2010 at 5:44am |
|
This can be used in the select expert to limit the data to returning rows in the date range of "2 years ago jan1" to "today - 2 years"
Edited by DBlank - 28 Apr 2010 at 6:32am
|
| IP Logged |
|
CMEPCMEP
Newbie
Joined: 26 Apr 2010
Online Status: Offline
Posts: 15
|
Posted: 28 Apr 2010 at 6:07am |
Originally posted by DBlank
This can be used in the select expert to limit the data to returning rows in the date range of 2 yeras agon jan1 to today - 2 years ago
I'm not sure what "returning rows" means. But are you saying to enter this as a formula in select expert? Also, keep in mind that I have a formula where I want to add the YTD, prior year and two years ago and then do some math to it, to get a two year avaerage. That's where I need this piece of information, or this additional year. I need all three pieces. It's like this right now:
If (({IM2_InventoryItemWhseDetl.YtdQtySold}+{IM2_InventoryItemWhseDetl.PriorYrQtySold})/15)*12 > 0 Then
(({IM2_InventoryItemWhseDetl.YtdQtySold}+{IM2_InventoryItemWhseDetl.PriorYrQtySold})/15)*12
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 28 Apr 2010 at 6:30am |
This all depends on your requirements.
You can use it in the select expert with an or statement to pull all 3 "sets" of dates (YTD, LastYTD or 2 years ago).
YOu can use it as a criteria from grouping your data rows
YOu can use it for a criteria in Running Totals or variable formulas.
From your formula above it appears that the DB is setting pre-calculated row of data for YTD and PriorYTD summaries. The solution recommended was about pulling all sales during a time period then summarizing in crystal.
Does that clarify?
|
| IP Logged |
|
CMEPCMEP
Newbie
Joined: 26 Apr 2010
Online Status: Offline
Posts: 15
|
Posted: 28 Apr 2010 at 6:50am |
Originally posted by DBlank
From your formula above it appears that the DB is setting pre-calculated row of data for YTD and PriorYTD summaries. The solution recommended was about pulling all sales during a time period then summarizing in crystal.
Yes, the DB is pre-determined but I have nothing for two years ago. That's the original post. I didn't have anything beyond last year.
Further, you are saying that that solution was summarizing in crystal?! I don't have the year to summarize in the first place! That was the whole point of the post.
And if I go to select expert using those databases or formula, that information is numbers not dates (and pre-determined). Doesn't it have to be consistent?
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 28 Apr 2010 at 7:31am |
The tone is getting a bit nasty. I am sorry if the free technical support you are getting is not up to your standards.
I did not answer your original post. I tried to answer your second question.
This is a crystal forum not a MAS90 forum. Why would I know that MAS90 stores no data for more than 2 years?
Most often crystal is used to summarize rows of data to get YTD totals. Most users do not already have YTD summarized data stored in a table in their DB. The original response was trying to give you help on how to dynamically select rows of data that almost any other user would have in the DB and need to get a summary. This is most oftne what people ask for, not an explanation of there DB schema.
You can try and re-post clearly defining that you need help finding data in the schema. Maybe someone does know your DB well enough to assist.
|
| IP Logged |
|
CMEPCMEP
Newbie
Joined: 26 Apr 2010
Online Status: Offline
Posts: 15
|
Posted: 28 Apr 2010 at 7:59am |
Sorry with the tone, I'm frustrated with my own stupidity. It's information I really need but keep hitting a wall (for months).
We have prior years information in Mas90, but I don't see the database to grab. So I guess the question is, can I get prior years data if I don't see a DB to access? Is there someway to get around it?
|
| IP Logged |
|
|
|
