Edited to include Attachment. Also edited the title to hopefully be more enticing...
I appreciate any help thinking through this problem that I have.
I have a database of orders made in a marketplace, where each member has an opportunity to order once a week. In this database, I also have a field 'Coupon', which indicates the type of Coupon used (there are potentially many types).
This table shows the example dataset:
To track the impact of each Coupon on successive orders, I want to be able to count the number of orders made by each member in the 3 weeks after they use a Coupon (including the week of the Coupon, so this count could take values between 1 and 4). In other words, orders made within 3 weeks of a Coupon are treated as having been impacted by that Coupon and I want to count these.
If each member could only use one Coupon, I can solve this problem with simple LoD. I can create a Fixed LoD at the [Member] level and calculate the [Week] that they used the Coupon. Then, any orders that came in between the Coupon [Week] and [Week] + 3 would be assigned some value to indicate that it was impacted by that Coupon.
However, like Member B in the example, some members could use multiple coupons, so I can't use a [Member] specific LoD calculation. I tried LoDs at the [Member] + [Coupon] level, but quickly found that I couldn't directly use them to find orders that don't have [Coupon] values but are impacted by a Coupon.
I also attempted something with Lookup(), but I need to aggregate these results and can't necessarily have it in the table form I have above.
I was thinking that a potential solution would involve creating some other field ('CouponEffect') in the table below, that would check whether the same Member had some coupon within the last 3 weeks, and assign the value of the Coupon if so. For example, in the Table below, for Member B, Week 9, since Member B had used Coupon Y within the last 3 weeks (at Week 6), it assigns it a value of Y to 'CouponEffect'. I could then used this 'CouponEffect' field in my calculations. However, I'm having trouble building the logic for this.
Any and all help is appreciated!
OrderCouponData.twbx 16.4 KB