not sure what I am missing in this case when state...
# suiteanalyticss
Suitestallion
03/31/2021, 6:49 PMnot sure what I am missing in this case when statement, I tried Tomorrow that didin't work, that's why I have today+1
CASE WHEN {expectedreceiptdate} BETWEEN {today} AND ({today}+6) THEN '<a href=https://.app.netsuite.com/app/common/search/searchresults.nl?searchid=3421&whence=>PO Delivering this week</a>' WHEN( {expectedreceiptdate}=({today}+1) then '<a href=https://.app.netsuite.com/app/common/search/searchresults.nl?searchid=3420&whence=> PO delivering Tomorrow</a>' WHEN (ROUND({expectedreceiptdate}-{today})) = 0 THEN '<a href=https://.app.netsuite.com/app/common/search/searchresults.nl?searchid=3232&whence=>Delivering Today </a>' else END
b
battk
03/31/2021, 7:03 PMit would be a lot easier to reason with your formula if you just did your comparisons against one statement
battk
03/31/2021, 7:03 PMlike make all your comparisons against
{expectedreceiptdate} - {today}battk
03/31/2021, 7:04 PMcurrently you have a bunch of statements that are probably equivalent in different way
s
Suitestallion
03/31/2021, 7:05 PMone of the line does show up so first statement is correct, and the result shows up, second and third is the one that is giving me the issue
b
battk
03/31/2021, 7:10 PMmy guess would be that the second guess is never true, its too specific
battk
03/31/2021, 7:11 PMand the third case is hidden because the first case covers it
battk
03/31/2021, 7:13 PM{expectedreceiptdate} - {today}s
Suitestallion
03/31/2021, 7:13 PMhmm, yes you are right but first case is Total for this whole rolling week, second statement is getting the list of things coming in tomorrow and third statement covers things coming in today
b
battk
03/31/2021, 7:14 PMyou need to understand that there are lots of different dates that correspond to tomorrow
s
Suitestallion
03/31/2021, 7:15 PMhmm yeah that's why i said Today+1
b
battk
03/31/2021, 7:15 PMyour second statement is checking if your expected ship date is one specific dcate
battk
03/31/2021, 7:15 PM2021-04-01T19:15:28.207Zbattk
03/31/2021, 7:16 PMin a couple of seconds
battk
03/31/2021, 7:16 PM2021-04-01T19:16:20.675Zbattk
03/31/2021, 7:16 PMi can basically guarantee your expected ship date is neither of those 2 dates
battk
03/31/2021, 7:17 PMthe precision of {today} is much more than you think it is
s
Suitestallion
03/31/2021, 7:18 PMhmm then how do you get list of POs delivering today or tomorrow? I thought Today and today+1 would do it, but I guess not
b
battk
03/31/2021, 7:19 PMi will once again recommend that you start with
{expectedreceiptdate} - {today}battk
03/31/2021, 7:19 PMyou can have a case for when its smaller than 1 (basically today)
battk
03/31/2021, 7:19 PMsmaller than 2, (tomorrow)
s
Suitestallion
03/31/2021, 7:19 PMok thanks, let me try that
b
battk
03/31/2021, 7:19 PMor smaller than 7 (next week)
battk
03/31/2021, 7:21 PMthere are nuances like most people wouldnt call a date 23 hours in the future today, but you didn't really make that distinction in your formula anyways
2 Views