This site was built to compliment the existing NetSuite Professionals Slack Community by adding a means for members to easily find answers to common questions and to create a searchable database of those questions and answers.

NetSuite Professionals

<@U011FC6BL9L> I haven't created one, but if it's for the current month, you could always code a " select * from {table} where month(datecolumn) = month(getdate()) and year(datecolumn) = year(getdate())" kind of logic

thank you Ricardo for responding, i was looking specifically into the sql formula on a NetSuite transaction saved search

if you're doing a saved search, then you don't need to use SQL-based code. just use the interface...

This is an example of one I use to see how many fulfillments were done this month.

my client wants the amounts to show in a column per day

what i came up so far was to hard-code the dates like this

yeah, so in your "results" tab, add the date into your results, and then "Group" that field

they need literally daily sales for a year

if that day dont have sales then let it show zero

and the day should be in column and not as rows

individual product sales by individual date? :zany_face: I got nothing for that, sorry.

yeah, one of your bigger problems is that the number of "columns" in your coding (for dates) will vary depending on how many dates there are each month... if you have one that resolves to an error, your whole saved search will likely fail (example february - only 28 days)

you might be able to this little bit better by only filtering on the "dateofthemonth" formatting and not the whole mm/dd/yy, and instead adjust your criteria to only pull where the date is current year, and date is current month. That way you only have to compare the actual datesofthemonth... you still have the "number of columns" issue

<@U011FC6BL9L> so you know... `case when to_char({trandate}, 'DD') = '01' then {amount} else 0 end` A quick test shows this does work, and would eliminate the full-date comparison issue. Invalid dates (such as feb 30th) just show 0. You'll still have to do this 31 times, but it'll work... you just have to set the initial criteria to month = current month (or even month = last month), and it works. I'd also change the label to just read the "date-of-month" instead of the full date readout for simplicity's sake.