Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
cjzimm76
Partner - Creator
Partner - Creator

Set Analysis where Date greater than another data field value

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.

Labels (2)
1 Solution

Accepted Solutions
rubenmarin

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))

View solution in original post

2 Replies
rubenmarin

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
Partner - Creator
Partner - Creator
Author

Thanks.  That worked.