Im trying to do a Saved Search for open purchase o...
# generals
Shel Singh
08/28/2020, 3:15 PMIm trying to do a Saved Search for open purchase orders…i have about 100 purchase orders with 5 different items coming to the warehouse within the next several months. I’m trying to figure out how I can pull this data by month into one report. So it will give me the total quantities for item 1 in September, October, November and December separately
s
Shel Singh
08/28/2020, 4:17 PMCan you send me to the right direction to create this formula please?
b
battk
08/28/2020, 4:21 PMi rarely will have anything on hand
battk
08/28/2020, 4:23 PMgeneral idea is to use a case statement where the case expression acts as filter where the month element retrieved using TO_CHAR is equal to the month you want
s
Shel Singh
08/28/2020, 5:15 PMcase when (to_char({expectedreceiptdate},‘MM’)),‘10’ then {quantity} else 0 end
Shel Singh
08/28/2020, 5:15 PMThis is what I came up with but its says invalid expression
Shel Singh
08/28/2020, 5:15 PMim putting it under formula numeric
b
battk
08/28/2020, 5:16 PMits the
,'10'battk
08/28/2020, 5:17 PMshould be checking that its '10' there
s
Shel Singh
08/28/2020, 5:17 PMi put the 10 in there because i wanted all the quantities for october
b
battk
08/28/2020, 5:18 PMwhat you have there is not a predicate, there is no condition that evaluates to true or false
👍🏻 1
s
Shel Singh
08/28/2020, 10:30 PMcase when (to_char({expectedreceiptdate},‘MMDDYYYY’))= ‘10’ then {quantity} else 0 end
Shel Singh
08/28/2020, 10:30 PMIm close
Shel Singh
08/28/2020, 10:32 PMcase when (to_char({expectedreceiptdate},‘MM’))= ‘10’ then {quantity} else 0 end
Shel Singh
08/28/2020, 10:32 PMGOT IT!!
3 Views