6 month forecast - How to handle different years? 1

[Mack2]

Within a report, I am showing a 6 month forecast. The first column is the first month. The second month is the current month plus 1, and so on for the 6 months.
How do I handle if the current month is december, then the next month is Jan, of a DIFFERENT YEAR? If all the months was the current year..easy, but what do I handle that fact it could be different years? THANKS!!!!!!

 

[PaulBricker]

Where are you filtering this report? In a query?, in the openreport command? It would help to know that.

Paul

 

[Crowley16]

how are you getting these months?

if you're just adding 1 each time, then you can use an integer division operation.

if you're getting the month from a date value, then why not just format the date as mmm/yyyy

--------------------
Procrastinate Now!

 

[Mack2]

I am using a query to get the months. I have the current and the second month's code below.
As you can see, I am not testing for year.

CurrentMonthLoad: Sum(IIf(Month(Date())=Month([request date]),[line qty],0))

SecondMonthLoad: Sum(IIf(Month(Date())+1=Month([request date]),[line qty],0))

THANKS FOR YOUR HELP!!!!!!!!!!!!!!!

 

[Crowley16]

instead of using month(date()), use a date serial instead, i.e.

month(
dateserial(
year(date()),
month(date())) + 1,
day(date())
)
)

this way, you'll actually build up a full date to compare instead of just comparing the month value


--------------------
Procrastinate Now!

 

[Mack2]

I am with you on the dateserial function Crowley16. But somehow I need to test if I am filter for next year, instead of this year. So if the current month is November...

Nov 2006(current) Dec 2006(current +1) Jan 2007(cur +2)

I just thought of test to see if month = 12, then year +1, seems like there has to be a better way

Thanks

 

[PaulBricker]

What is [Request Date]? Your expressions above don't make sense to me. How can this expression

Month(Date())=Month([request date])

and this expression

Month(Date())+1 = Month([request date])

both be true.

Just curious and it might help produce a solution that does what you need.

Paul

 

[Mack2]

Those are two different fields(columns) within the query. I have 6 different fields like that ranging from current all the way to month plus 5. I showed you examples of the first two fields. Hope this helps

 

[PaulBricker]

Yes, I understand that they are differrent columns used to make up your 6 month span, but my question is how can Month(Date())(like February) and Month(Date())+1(like March) both equal Month([Request Date]). It just seems to me that the first expression above would sum up the [line qty] field, and the rest would produce 0's. So I'm trying to determine exactly what [Request Date] is and how your query uses it to get the data summed up. It probably makes sense, I'm just not understanding it.

Thanks

Paul

 

[Mack2]

Paul, sorry, I noticed that not all of the formula was copied. There should be an IIF in there
IIF(Month(Date())=Month([request date])).
So if the request date is in the same month it would grab it. Then I would sum the totals

 

[PaulBricker]

OK, sorry, I see what's happening. [request date] is another field in the query you are running your columns against.
You can use something like this.

IIf(Month(Date())+1>12,[blue]"Do something here"[/blue],Sum(IIf(Month(Date())+1 = Month([request date]),[line qty],0)))

Are you trying to do something if we cross into a new year?

Paul

 

[Mack2]

Yes, if we cross into a new year then I need to get the data from that month and the new year. Not the month and current year. I think your Month(Date())+1>12 part of the formula is what I need. I will test it out and let you know. The formula from above that I listed is not filtering out for the correct year. It will return data from the month and ALL years.

THANKS FOR YOUR HELP. Let me know if you have any other questions

 

[PaulBricker]

Or you could do something like this.
In a new column in your query put this

myDate:Format([request date],"mm/yyyy")

In the next column put

CurrentMonthLoad:Sum( IIf(Format(Date(),"mm/yyyy")=[myDate],[line qty],0)

then in the next

SecondMonthLoad:Sum(IIf(Format(DateAdd("m",1,Date()),"mm/yyyy")=[myDate],[line qty],0)


ThirdMonthLoad:Sum(IIf(Format(DateAdd("m",2,Date()),"mm/yyyy")=[myDate],[line qty],0)

and on down.

This should take care of year issues.


Paul

 

[PaulBricker]

Sorry, I think I forgot the closing ) for the Sum expression. There should be two )) at the end of each argument.

Sum( IIf(Format(Date(),"mm/yyyy")=[myDate],[line qty],0)[blue])[/blue]


Paul