Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I am trying to figure out a set statement for the following rule
IF [Created Date] <= current select, AND [Close Date] > current select, OR [Order Status] -= 'Cancelled', 'Rejected', sum sales, 0
the end goal is to get a statement that sums up all open orders on any given day.
I don't even know if this is the best way to achieve this but due to the nature of open orders constantly changing I need to be able to have a progressive view on it.
thank you
Try something like this:
Sum({<[Created Date]={"<=$(vCurrentSelect)"},[Close Date]={">=$(vCurrentSelect)"}>+<[Order Status-={'Cancelled','Rejected'}>}Sales)
Hm, maybe like
= sum({<[Created Date] = {"<=$(=max(Date))"},[Close Date] = {">$(=max(Date))"}, [Order Status] -= {'Cancelled','Rejected'}>} Sales)
if Date is your date field to select from. I also assumed a AND [Order status] above given your textual description (all open orders on any given date). If there is only one OPEN status, you could use [Order Status] = {'Open'} instead above part.
Regards,
Stefan
P.S: You probably need to clear some fields also, like Date, more Date fields?
= sum({<[Created Date] = {"<=$(=max(Date))"},[Close Date] = {">$(=max(Date))"}, [Order Status] -= {'Cancelled','Rejected'}, Date= >} Sales)