Trying to figure out a way to create a report for new customers. By new customers, I mean customers that were created within a time frame, say the last 3 months. I want to see what my sales are for these new customers.
Currently, I’ve got my customer table linked into my sales database. Each customer record has a “Created Date” field. This field is currently pulled in as a property. I can create the view I’m looking for by using the advanced search, and filtering based on the “Created Date” property. The problem is I’m having to specify a date in the advanced search. So I have to keep changing this date. So I’m trying to think of a more creative solution for this, but I so far I’ve come up with nothing.
I don’t know how creative my suggestion is 
… Not necessarily “new” customers, but ones that only have recent activity, by maybe using offset dating and look for no sales prior to 3 month ago but with recent sales? Something like below:
And go back further than 15 months ago if you need.
I see where your going here. I’ll have to think on it a bit more. It does seem that this would miss any customers that opened an account recently and haven’t done any business yet.
@aaron.roma Do you have the info in your ERP as to the date of the account opening? If you pull that in as a Property, I wonder if you could do an advanced search to pull any customers into the report that have been created within a certain time period based on that Property. We currently have their date of opening the account in as a property called “Start Date”, but I can’t figure out how to filter it. That property is currently a text field, so maybe the solution would be to classify it as a date field and then filter somehow. Hope this helps, though a bit late!
@tim.janes
Yes, we are already pulling in the created date as a property. It’s even pulled in as a date field. I can do an advanced search and filter based on this property. The issue with the advanced search is that you have to specify a specific date. Lets say I want to see the customers created in the last 6 months. I can filter where “Created Date” > “Specific Date 6 Months Ago”. However, next month, I have to go back in and update that specific date to reflect a new 6 month period. I can’t find a way to do it with relative dates.
1 Like
@aaron.roma
That’s kind of what I figured would happen with that route. Wish I had an answer for relative dates in advanced search! Perhaps that’s a functionality that the Phocas team can look into developing?
I have just added Account Created Date as a property to my data, so Monday I will be playing around with this as we are currently relying on an SQL report that doesn’t get run each month to see this data, so am interested in automating this process with Phocas 
Good luck! Let me know if you come up with anything interesting. I’ve still not come up with a good solution other than going back in and manually modifying the filter each month.
So my theory is if you add the Account Opened Date as a measure then you can use Custom Mode to use offset dates, I currently have my data as a Property, I am going to move it to Measures and test this theory tomorrow. 
Scratch that, can’t add Account Created Date as a measure 
I wonder if you could add that data as a Dimension? I know in my warehousing database we had “Day Of Week” and “Hour of Day” as Dimensions. Not sure it would play nicely like those, since individual dates seem like a different type of property data.
Wouldn’t let me put it anywhere other than Properties. 
I was thinking this morning, I am reporting on new customers by branch, maybe I make it a YTD report so I only need to update the filter once a year, it would make more sense this way as some of our branches don’t get new customers each month anyway.
1 Like
If you have access to DB Designer you can add a transform column called something like DaysSinceOpen
In your transform column
DATEDIFF(d,[AccOpenDate],GETDATE()) – Change column to match your data.
Map this new column as property of customer and set it as number, you can then use this in advanced searches on the property eg
[DaysSinceOpen] property is < 90
4 Likes
Ah! @anon66559099, that’s a pretty good suggestion. I’ll have to give that a try when I get a chance. In the end, it would be nice to be able to use offset dates in the advanced search rather than just hard dates, but this is a nice workaround.
Just to follow-up, @anon66559099, this suggestion worked out great! I really appreciate it. This actually opens a whole new door for me, as I had no idea you could use SQL functions in the transform columns. I only knew you could do basic arithmetic and concatenations, things of that nature.
1 Like