https://netsuiteprofessionals.com logo
Join Slack
Powered by
Hi all! Is there any way in either a saved search ...
# suiteanalytics
l
Hi all! Is there any way in either a saved search or using a query, that I can have a search return a list of customers with the total count of phone calls within a set date period, but with multiple columns for different date periods? I can get it working with just one column, but I need multiple ideally. For example, columns would be: "Customer ID", "Count of calls Last 30 Days", Count of calls Last 90 days", etc. Currently this is done via a script which calls many searches (one for each customer...) and it's obviously slow, so hoping to get it faster by having one master search/query
m
Do a formula numeric CASE WHEN {today} - {phonecall.date} =< 30 THEN 1 ELSE 0 END
Then use summary type SUM on that column
Repeat for all your requirements but just change the =< xx to the correct days
l
Thanks for the response, and I have tried that earlier today but it shows "ERROR: Invalid Expression" in that column
Interestingly, when the summary type is Count, it has no error and shows a 1 in that column (tells me it can correctly check that there have been calls during that date period, but it's not totaling them)
m
Just try doing a formula of {today} - {phonecall.date} what does that return
(Not sure what the correct field for the phonecall date is but I’m hoping you have. That already)
l
call.createddate is the field
{today} - {call.createddate} makes it sum the entire difference in dates of all calls
m
But without summary does that formula return the correct numeric value?
l
Without a summary type it doesn't have a column at all
m
Oh sorry I was trying to build it as a non summary search to validate the calculations. Then we would summarize at the end So you would need to remove the summary type from all columns
l
Removing all summary types from all result columns makes it so that every row is a call, and that formula column provides the difference in dates
m
Ok perfect Can you add a second column with the formula CASE WHEN {today} - {call.datecreated} < 30 THEN 1 ELSE 0 END What does this column show?
l
lol, unexpected error...
m
Is it a formula numeric?
l
Yep, formula(Numeric) and nothing has summary type
m
Try putting TO_NUMBER() around the subtraction
Like TO_NUMBER({today}-{datecreated)
l
Yeah, still unexpected error
m
Does the phone call have a start date or end date field?
Also try using TO_DATE on date created. TO_DATE({call.datecreated},’MM/DD/YYYY’)
l
Okay got it working
Well, to this point, not fully
m
If this works hopefully you can add your other columns now for 90 days etc
l
So it now shows each row as a call, with {today}-{call.createddate} showing the date period difference, and the CASE WHEN column showing 1 for each correct row (since it's just one call per row)
m
Then add the summarization back on
Nice
l
Oh
We might be there
m
E excellent
l
God damn, that's a relief!! Thank you so much for your help
m
👍 glad you got it
l
Ahhhh
Guess what the issue was...
=< vs <= . Always the simple things
m
Oh my bad 🤦
l
Appreciate you working through it step by step with me
Hit a new roadblock on this journey in case you have time to help again, no worries if not though. I'm slapping this search into a map/reduce script (to make it faster, hopefully). The issue is that the mapContext object passed to map() only seems to hold one of the columns
2 Views
Open in Slack
PreviousNext
Powered by