Today's fun formula for the day:
```sum/* comment ...
# suiteanalyticsk
KevinJ of Kansas
08/13/2020, 1:29 PMToday's fun formula for the day:
Copy code
sum/* comment */(case {transaction.type} when 'Purchase Order' then ({transaction.quantity}-nvl({transaction.quantityshiprecv},0)) when 'Sales Order' then -({transaction.quantity}-nvl({transaction.quantityshiprecv},0)) else 0 end) OVER(PARTITION BY {internalid},{transaction.location} ORDER BY {transaction.trandate} ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) +nvl({locationquantityonhand},0)😀 1
v
Vtor
08/13/2020, 2:45 PMooooh lemme find you one of the ones from my predecessors
Vtor
08/13/2020, 2:48 PMthis is because those who came before didn't know about the native quota functionality and decided to come up with custom quotas via saved search formulas
(CASE WHEN NVL(SUM((CASE WHEN {transaction.type} in ('Invoice','Credit Memo') AND {transaction.trandate} >= ADD_MONTHS(TRUNC({today}, 'MONTH'), -12) AND {transaction.trandate} < ADD_MONTHS(TRUNC({today},'MONTH'), -11) THEN {transaction.amount} - NVL({transaction.shippingamount},0) - NVL({transaction.taxamount},0) ELSE 0 END)*(1 + {salesrep.custentity_hub_sales_quota_p})),0) = 0 THEN 1 WHEN (SUM(CASE WHEN {transaction.type} in ('Invoice','Credit Memo') AND {transaction.trandate} >= TRUNC({today}, 'MONTH') THEN {transaction.amount} - NVL({transaction.shippingamount},0) - NVL({transaction.taxamount},0) ELSE 0 END)) = 0 THEN -1 ELSE (SUM(CASE WHEN {transaction.type} in ('Invoice','Credit Memo') AND {transaction.trandate} >= TRUNC({today}, 'MONTH') THEN {transaction.amount} - NVL({transaction.shippingamount},0) - NVL({transaction.taxamount},0) ELSE 0 END) - SUM((CASE WHEN {transaction.type} in ('Invoice','Credit Memo') AND {transaction.trandate} >= ADD_MONTHS(TRUNC({today}, 'MONTH'), -12) AND {transaction.trandate} < ADD_MONTHS(TRUNC({today},'MONTH'), -11) THEN {transaction.amount} - NVL({transaction.shippingamount},0) - NVL({transaction.taxamount},0) ELSE 0 END)*(1 + {salesrep.custentity_hub_sales_quota_p})))/ABS(SUM((CASE WHEN {transaction.type} in ('Invoice','Credit Memo') AND {transaction.trandate} >= ADD_MONTHS(TRUNC({today}, 'MONTH'), -12) AND {transaction.trandate} < ADD_MONTHS(TRUNC({today},'MONTH'), -11) THEN {transaction.amount} - NVL({transaction.shippingamount},0) - NVL({transaction.taxamount},0) ELSE 0 END)*(1 + {salesrep.custentity_hub_sales_quota_p})))END)+1👀 1
Vtor
08/13/2020, 2:49 PMlook at the length on that bad larry
k
KevinJ of Kansas
08/13/2020, 3:42 PMSeems like a lot of the same logic over and over.