Help with data selection over multiple time periods 1

[MJV8198]

I have a booking report that currently compares YTD booking with Prior YTD Booking. The complete selection formula is as follows and is working without problems.
{oeel1.custno} <> 100052.00 and
({oeel1.enterdt} in LastYearYTD or
{oeel1.enterdt} in YearToDate) and
{oeeh1.transtype} in ["CR", "DO", "RM", "SO"] and
{oeeh1.cono} = 1 and
({oeeh1.stagecd} in [1,9] or ({oeeh1.stagecd} in [ 2, 3, 4, 5] and {oeel1.qtyship} > 0)) and
{sasta1.codeiden} in ["c"]

So this selection grabs all records from 01/01/last year to today's date and
from 01/01/Current year. This of course is obvious to all of you but it will help me explain the next selection.

I have been asked to make an adjustment to include orders entered prior to 01/01/last year but were invoiced last year. So I created this selection independent of the the first selection.

{sasta1.codeiden} in ["c"] and
{oeel1.custno} <> 100052.00 and
{oeel1.enterdt} > (Date(Year(CurrentDate)-2, 1, 1)) and
{oeel1.enterdt} < (Date(Year(CurrentDate)-2, 12, 31)) and
{oeeh1.transtype} in ["CR", "DO", "RM", "SO"] and
{oeeh1.cono} = 1 and
({oeeh1.stagecd} in [1,9] or ({oeeh1.stagecd} in [ 2, 3, 4, 5] and {oeel1.qtyship} > 0)) and
{oeel1.invoicedt} > (Date(Year(CurrentDate)-1, 1, 1))

This seems to work on its own as well. My problem is when I try to combine the formulas it bombs out.

Sorry this probably is a simple solution but i am just learning.

 

[pmax9999]

Try:

{oeel1.custno} <> 100052.00 and
(
	{oeel1.enterdt} in LastYearYTD or
 	{oeel1.enterdt} in YearToDate or
	(
		{oeel1.enterdt} > (Date(Year(CurrentDate)-2, 1, 1)) and
 		{oeel1.enterdt} < (Date(Year(CurrentDate)-2, 12, 31)) 
	)
) and
{oeeh1.transtype} in ["CR", "DO", "RM", "SO"] and
{oeeh1.cono} = 1 and
(	
	{oeeh1.stagecd} in [1,9] or 
	(
		{oeeh1.stagecd} in [ 2, 3, 4, 5] and 
		{oeel1.qtyship} > 0
	)
) and
{sasta1.codeiden} in ["c"]

Cheers
Pete

 

[MJV8198]

Hi Pete thank you for posting!

Good news and bad news, the selection did not bomb! the bad news is I am picking up all booking data from 2012 and all the booking data from YTD 2013. All i need from 2012 are records where {oeel1.enterdt} is from 2012 but the {oeel1.invoicedt} is from 2013.

This is closer than i have been in the past.


Mike

 

[pmax9999]

Sorry, missed that bit from your post. Try this:

{oeel1.custno} <> 100052.00 and
(
	{oeel1.enterdt} in LastYearYTD or
 	{oeel1.enterdt} in YearToDate or
	(
		{oeel1.enterdt} > (Date(Year(CurrentDate)-2, 1, 1)) and
 		{oeel1.enterdt} < (Date(Year(CurrentDate)-2, 12, 31)) and
		{oeel1.invoicedt} > (Date(Year(CurrentDate)-1, 1, 1)) 
	)
) and
{oeeh1.transtype} in ["CR", "DO", "RM", "SO"] and
{oeeh1.cono} = 1 and
(	
	{oeeh1.stagecd} in [1,9] or 
	(
		{oeeh1.stagecd} in [ 2, 3, 4, 5] and 
		{oeel1.qtyship} > 0
	)
) and
{sasta1.codeiden} in ["c"]

Cheers
Pete

 

[MJV8198]

Ok Pete you are the best! it worked perfectly.

I have one question, does the formatting, meaning the indents make a difference? I have tried many combinations so I cannot recall if I have tried this specific formula but it is rather simple solution. The main difference would be the indentations you used. I copied and pased the formula exactly as it appears.

Thanks again for your help.


Michael

 

[pmax9999]

Michael

Glad it helps.

The formatting/indents within the formula make no difference.

The critical part when mixing and with or is the use of brackets to ensure the correct order of processing. I use indents as I find it makes it easier to read as it helps with visualising the processing order.

Cheers
Pete

 

[MJV8198]

Hi Peter

I hate to impose as you have been very helpful. As I worked through the detail I realized I was still missing some transactions. so as ot currently stands I have the following.

{sasta1.codeiden} in ["c"] and
{oeel1.custno} <> 100052.00 and
{oeeh1.transtype} in ["CR", "DO", "RM", "SO"] and
{oeeh1.cono} = 1 and
(
{oeeh1.stagecd} in [1,9] or
(
{oeeh1.stagecd} in [ 2, 3, 4, 5] and
{oeel1.qtyship} > 0
)
) and
(
{oeel1.enterdt} in LastYearYTD or
{oeel1.enterdt} in YearToDate or
(
{oeel1.enterdt} > (Date(Year(CurrentDate)-1, 1, 1)) and 1- I added this line
{oeel1.enterdt} < (Date(Year(CurrentDate)-1, 12, 31)) and 2- I added this line
{oeel1.invoicedt} > (Date(Year(CurrentDate)-1, 12, 31)) 3- I added this line but it needs adjustment see below
) or
(
{oeel1.enterdt} > (Date(Year(CurrentDate)-2, 1, 1)) and
{oeel1.enterdt} < (Date(Year(CurrentDate)-2, 12, 31)) and
{oeel1.invoicedt} > (Date(Year(CurrentDate)-1, 1, 1))
)
)



I added the three line noted above because i need the records entered after today but invoiced this year. this change resolved that problem but i am still missing additional transactions. The transactions I am missing were entered last year but have not yet been invoiced date.

So what I think I need a line that allows any transaction entered last year where the invoice date is Not in last year. However I do know how to do not in. any ideas?

 

[pmax9999]

Hi Mike

Don't have a lot of time at the moment, so it may need a bit of fine tuning, but try this:

(
	Isnull({oeel1.invoicedt}) or
	{oeel1.enterdt} in LastYearYTD or
 	{oeel1.enterdt} in YearToDate or
 	( 
		{oeel1.enterdt} > (Date(Year(CurrentDate)-1, 1, 1)) and 1- I added this line
 		{oeel1.enterdt} < (Date(Year(CurrentDate)-1, 12, 31)) and 2- I added this line
 		{oeel1.invoicedt} > (Date(Year(CurrentDate)-1, 12, 31)) 3- I added this line but it needs adjustment see below
 	) or
 	(
 		{oeel1.enterdt} > (Date(Year(CurrentDate)-2, 1, 1)) and
 		{oeel1.enterdt} < (Date(Year(CurrentDate)-2, 12, 31)) and
 		{oeel1.invoicedt} > (Date(Year(CurrentDate)-1, 1, 1)) 
	)
) and

{sasta1.codeiden} in ["c"] and
{oeel1.custno} <> 100052.00 and
{oeeh1.transtype} in ["CR", "DO", "RM", "SO"] and
{oeeh1.cono} = 1 and
( 
	{oeeh1.stagecd} in [1,9] or 
	(
 		{oeeh1.stagecd} in [ 2, 3, 4, 5] and 
		{oeel1.qtyship} > 0
 	)
)

I added a test for Null Invoice Dates. I changed the order so I could put the Null test first, which is generally required.

Cheers
Pete

 

[MJV8198]

Hi Peter,

I tried the Null In the place you located it it was pulling too many records. I tried a couple other places. The good news is I was able to find another variable that allowed me to do the report. I had a few other challenges getting all the data sorted out.

I would not have complete the project without your assistance