Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

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.

1 Solution

Accepted Solutions
Highlighted

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
Highlighted

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

Highlighted
Partner
Partner

Thanks.  That worked.