Check whether a specific transaction took place within a time period

[TamirBasin]

Hi,

How can I calculate whether a specific transaction took place within a time period of one of the Sales (Discounts and allowances table)?

Thanks,
Tamir

Screenshot

My File
https://basintamir-my.sharepoint.com/personal/tamir_tamirbasin_com/_layouts/15/guestaccess.aspx?docid=1976097f9d69a41769e47a1a9afb422de&authkey=AY61E3tYtZC1R4f_tTpVEnE

 

[Peter_SSs]

Assuming the bottom right table is called "Table3", then try this in the top row of your blue table.

=IF(COUNTIFS(Table3[StartDate],"<="&[@Date],Table3[EndDate],">="&[@Date],Table3[Products],[@Product]),"Yes","No")

 

[TamirBasin]

Elegant !

It works perfectly.

Thank you

Tamir

 

[Peter_SSs]

Elegant !

It works perfectly.

Thank you

Tamir

You are very welcome. Thanks for letting us know.

 

[TamirBasin]

Hi Peter,

I hope it is ok to continue this thread as it is related...
(question is referring to the above screenshot)

After getting the result whether a transaction/date is included within the time period of a Promotion period, I need to retrieve the name of the aproprate Promotion name.
I cannot figure a way to do it and would appreciate some help.

Thank you,
Tamir

 

[Peter_SSs]

I hope it is ok to continue this thread as it is related...

Yes, it is closely related so fine to continue in this thread.

Try this.

Book1
	B	C	D	E	F	G	H	I	J	K
2
3	Date	Product	Price	InDisc	Promotion
4	5/10/2017	Prod1		Yes	HappyHolidays
5	5/10/2017	apple		No	N/A
6	6/10/2017	Prod2		Yes	HappyHolidays
7	15/10/2017	Prod2		No	N/A
8	15/10/2017	Prod3		No	N/A
9	15/10/2017	Prod4		Yes	OctoberSale		Promotion	StartDate	EndDate	Products
10							HappyHolidays	1/10/2017	10/10/2017	Prod1
11							HappyHolidays	1/10/2017	10/10/2017	Prod2
12							HappyHolidays	1/10/2017	10/10/2017	Prod3
13							OctoberSale	15/10/2017	29/10/2017	Prod1
14							OctoberSale	15/10/2017	29/10/2017	Prod4
15
Sale Periods

Cell Formulas
Range	Formula
E4	=IF(COUNTIFS(Table3[StartDate],"<="&[@Date],Table3[EndDate],">="&[@Date],Table3[Products],[@Product]),"Yes","No")
F4	=IF([@InDisc]="Yes",INDEX(Table3[Promotion],AGGREGATE(15,6,(ROW(Table3[Promotion])-ROW(Table3[[#Headers],[Promotion]]))/((Table3[StartDate]<=[@Date])*(Table3[EndDate]>=[@Date])*(Table3[Products]=[@Product])),1)),"N/A")

 

[TamirBasin]

Hi Peter,

Thank you for the great formula. It works great!

Could you please explain a little regarding the expression:
AGGREGATE(15,6,(ROW(Table3[Promotion])-ROW(Table3[[#Headers],[Promotion]]))
I understand that the ROW returns an array of the rows numbers and that SMALL returns the smallest item. But....I lose the plot here...

and why would you divide it by the product of:
((Table3[StartDate]<=[@Date])*(Table3[EndDate]>=[@Date])*(Table3[Products]=[@Product]))
I understand that I get 0 for no match and 1 for a match.

Thank you,
Tamir

 

[Peter_SSs]

You are almost there with your understanding.
You are correct the the (ROW(Table3[Promotion])-ROW(Table3[[#Headers],[Promotion]]) returns an array of numbers: 1,2,3,.. up to however many rows in the table.
You are also correct that the divisors are either 0 or 1. The divisor is 1 when all our required conditions are met (ie correct product, correct date range) otherwise 0.
When the divisions are done all the zeros (rows we are not interested in) produce errors.
The second argument of the AGGREGATE function (6) says to ignore those errors. Hence the AGGREGATE returns the smallest row where all our conditions are met. I would expect that there would only ever be one row that meets all the conditions but if a product/date fitted more than one promotion, the current formula would return the first matching one found in the other table.

 

[TamirBasin]

Thank you Peter,

I understand now the reasoning for the division.

The expression ROW(Table3[[#Headers],[Promotion]] always returns {1} as it is the location of the header row, so it is always the same e.g. {1}

The expression ROW(Table3[Promotion]) will also (to my understanding) returns always the number of rows in the table....so why would it give me the right line number as my first item in the array of row numbers? How does it filter?

Thank you,
Tamir

 

[Peter_SSs]

The expression ROW(Table3[[#Headers],[Promotion]] always returns {1} as it is the location of the header row, so it is always the same e.g. {1}

Not so. For the layout in my screen shot ROW(Table3[[#Headers],[Promotion]] ) returns 9 (the worksheet row of the table headrer), not 1.
Check by putting the formula in a worksheet cell =ROW(Table3[[#Headers],[Promotion]])

The expression ROW(Table3[Promotion]) will also (to my understanding) returns always the number of rows in the table....

Not so. It returns array of the worksheet row numbers of the table. Again for Table3 of mine that would be {10,11,12,13,14}

So when we do the subtraction of 9 we gete {1,2,3,4,5} which are the rows of the table, relative to the table.