Set Analysis where Date greater than another data field value

[cjzimm76]

Hi,

I'm trying to write a set analysis statement where I sum the QTY field.  Each customer has a start date and end date for a promotion.  So I want to Sum the QTY where the Order Date is equal to or greater than the promotion start date and equal to or less than the end date .  Each customer has their own start date.  I thought it would look something like this:

=Sum({<[OrderDate] = {'>=$(=(Date(Max([StartDate])))) <=$(=(Date(Max([EndDate]))))'}>} QTY)

But this and other variations are not working.  Please help.

[rubenmarin · Accepted solution]

Hi ,the $-expansion is calulated before the object, so it will apply a fixed date for all records, in this case it will be like doing this expression: =Sum({<[OrderDate] = {'>=1/3/2020 <=1/7/2020'}>} QTY)

You can check it removing the label from the table and see how the expression ends.

An alternative can be flag the records in script to create a field "1 as isPromotion" when order date is between the start date and end date of promotions for the customer, with applymap or joins you can bring the promotions dates to the order dates. Doing this you can use a simpler expression like: Sum({<{isPromotion={1}>} QTY)

With the current model you can use an expression like:

=Sum(Aggr(If(OrderDate>=StartDate and OrderDate<=EndDate, QTY), Customer, OrderDate))

[cjzimm76]

Thanks.  That worked.