Show only if the Sum of orders for a particular date is <=15

[djbloiss]

I need to create a set analysis that will only show a line if the sum of the orders on any date for a customer are less than or equal to 15 cases or less than or equal to 650 dollars.

This is what I originally had but now looking at it again it does not seem to be the correct logic. I think what this is doing is checking the customer for that date range, if the sum of its cases are less than or equal to 50 or less than or equal to 650 dollars.  I need it to look at each day separately.

sum({$<[Customer Adj]={"=(sum({$<$(vSO_Closed_Only),[Transaction Date]={'>=$(=date(vxToday-35))'}>} [Cases_Aggr])<=15 or sum({$<$(vSO_Closed_Only),[Transaction Date]={'>=$(=date(vxToday-35))'}>} [Sales_Aggr])<=650)"} >}

[Cases_Aggr])

How do I change it so it compares the orders by day instead of within the range I put?

[niclaz79]

Hi, I find a sum - if statement combined with an aggr to work better and easier in situations like this;

Aggr(Sum(if(Count(Cases_Aggr)<=15 or Sum(SalesAggr) <650, Cases_Aggr)),[Transaction Date])

[djbloiss]

Hi Niclas,

Thank you, the logic looks correct but it is coming up with no records.  I did have to make one change:

Aggr(Sum(if(Count(Cases_Aggr)<=15 or Sum(Sales_Aggr) <650, Cases_Aggr)),[Transaction Date])

Do you know why it may be coming up with no records, when I know there are some?

[sunny_talwar]

Is this used as a dimension or expression?

[niclaz79]

Hi,

It's very hard to say. Can you provide a version of your application that I can download and try out? It's hard to fix issues without seeing the data itself

[djbloiss]

Sunny it is an Expression.

[djbloiss]

Niclas,

I am not able to, the file is way to large (a couple of gigs) and too complicated.

[sunny_talwar]

I see only two conditions... what is the actual expression here? May be try like this

Sum(Aggr(If(Sum({$<$(vSO_Closed_Only), [Transaction Date] = {">=$(=date(vxToday-35))"}>} [Cases_Aggr]) <= 15 or Sum({$<$(vSO_Closed_Only), [Transaction Date] = {">=$(=date(vxToday-35))"}>} [Sales_Aggr])<=650, [Cases_Aggr]), [Customer Adj], [Transaction Date]))

[djbloiss]

Sunny,

That gives me values that look correct.  Now I need to add one more condition.  I need to filter out any [Customer Adj] that have [Sales_Aggr] > 2000 over the last 7 days.

[sunny_talwar]

So, you want to include those which have less than 2000 over the last 7 days?