1

I am trying to create a formula that will show all orders for customers that have an order yesterday.

So if the customer has an order yesterday, show all orders for that customer but if a customer has no order yesterday then show nothing. This is the formula that I have used but it isn't customers that only had orders yesterday:

sum({$<[Customer Adj]=p({<[Invoice Date Num]={$(=vxToday-1)}>}), [Originating cl for order]-={'B', 'E', 'P'}, BilltoShiptoSales_Flag={0} >} 
[Cases_Aggr])

I will then need to limit this by only show the previous week of their orders but I can figure that out.

Purpose of this: The reason for this is at my job some customers abuse Friday delivery's and as a result we have way too many Friday delivery's. We are trying to see all the customers who got Friday delivery's and then see if they get a large order earlier in the week that warrants their Friday delivery. If not, maybe we can get them to get their order earlier in the week.

picture of result: enter image description here

djblois
  • 963
  • 1
  • 17
  • 52
  • have you tried "sum({$<[Customer Adj]=p({<[Invoice Date Num]={$(=vxToday-1)}>} [Customer Adj]), [Originating cl for order]-={'B', 'E', 'P'}, BilltoShiptoSales_Flag={0} >} [Cases_Aggr])" – EldadT Apr 27 '18 at 12:10
  • @EldadT same result – djblois Apr 27 '18 at 12:17
  • the maybe this part is not giving the correct result "$(=vxToday-1)" add a new expression with that calc, don't use anything for the label. then in the table chart, you can see what expression was rendered (you will see it in the header) – EldadT Apr 27 '18 at 12:23

1 Answers1

0

another way of doing this:

  1. create a variable that stores all customers that have an order yesterday: vCustomers =concat({<[Invoice Date Num]={$(=vxToday-1)}>} DISTINCT chr(39)&[Customer Adj] &chr(39),',')

  2. your expression now will be: sum({$<[Customer Adj]={$(vCustomers)}, [Originating cl for order]-={'B', 'E','P'}, BilltoShiptoSales_Flag={0} >} [Cases_Aggr])

EldadT
  • 912
  • 7
  • 21
  • The variable is working but the expression is not; it is giving me an error that says, "Error in set modifier ad hoc element list: ',' or ')' expected" – djblois Apr 27 '18 at 14:09
  • add a new expression with that calc, don't use anything for the label. then in the table chart, you can see what expression was rendered (you will see it in the header) . copy it here – EldadT Apr 27 '18 at 14:22
  • I can't because the table is not showing up, it is just giving me the error, "Error in set modifier ad hoc element list: ',' or ')' expected" – djblois Apr 27 '18 at 14:25
  • then put a screenshot of the expression. something is wrong with the expression itself. nothing else. if you remove the variable does it work? – EldadT Apr 27 '18 at 14:48
  • Eladt, I am no longer getting the error but I am receiving no results but I should be. This I copied from my application: sum({$<[Customer Adj]=p({<[Invoice Date Num]={$(=vxToday-1)}>}), [Originating cl for order]-={'B', 'E', 'P'}, BilltoShiptoSales_Flag={0}, [Invoice Date Num]={">=$(=vxToday6)<=$(=vMax_SO_Closed_Date-2)"}>} [Cases_Aggr]) – djblois Apr 30 '18 at 12:31
  • start eliminating parameters and look where it gets no results. add one parameter at a time. I know that the above solution works as I use it all the time, so check the other parameters. It can also be the date format... – EldadT Apr 30 '18 at 14:43