I have specific customers in a program that initiated at a specific date 2 years ago. Each enrolled in the program on that day or after. I would like to only capture sales after their enrollment. How can I do this, in where can I set this condition or is this something custom in the backend? thank you
Hi @avazquez1 -
I think this is totally possible as long as the enrollment date can be added to your extracts. If you’re an admin with DB Designer experience, then everything that follows might make sense to you. If not, then basically this would indeed be a backend function in my opinion.
If the “Customer Enrollment Date” is something that is part of the Sales Transactions extract from your ERP, or can somehow be added as a column to that extract, then theoretically this could be accomplished on DB Designer using an Expression transform column on the transactional data with a SQL statement. My thought is for each transaction line the statement would evaluate “if enrollment date exists and transaction date is equal to or after that enrollment date, then show the sales value of the transaction, if not then show 0”. Then that transform column can be mapped in as a new Measure called “Post-Enrollment Sales” or something. If your SQL skills are lacking (like mine), then Google and/or all the AI tools out there can be very helpful with this 
An additional step would be to add a way to easily “slice” into your customer list that is enrolled in the program. I do this often with either a transform column on the Customer extract, or just manually uploading (or syncing) a list of customers with a “Y” next to them and mapping that as a sub-dimension of the Customer dimension.
I don’t know of a way on the front end to ask the software to pull a measure based on a dynamic date that would change per customer, but I imagine if it were possible, it would have to be done using SQL on a Custom column. Even if that did exist, I imagine the customer’s enrollment date would have to be available on the database to be referenced, again either through extracts or a manually uploaded/synced spreadsheet.
There are a bunch of smarter people on here with more SQL experience, especially in using SQL within Custom columns, so maybe somebody else will get a better idea from this to help you out!
Tim
1 Like
Tagging @JonKemp because I feel like he would have good input on my reply!
1 Like
Hi @avazquez1 ,
I agree with @tim.janes that the cleanest approach is to either update the SQL sync load to have a case statement or add an expression in teh designer, such as this (assume “company” is the deciding factor to define date cutover):
Select
MyDate,
Company,
SalesAmount,
FieldA,
FieldB,
…,
CUTOVERDimension =
CASE Company
WHEN ‘US’ THEN CASE WHEN MyDate >= ‘2023-01-01’ THEN ‘Cutover’ ELSE ‘Ignore’ END
WHEN ‘NZ’ THEN CASE WHEN MyDate >= ‘2022-09-01’ THEN ‘Cutover’ ELSE ‘Ignore’ END
WHEN ‘GB’ THEN CASE WHEN MyDate >= ‘2023-03-01’ THEN ‘Cutover’ ELSE ‘Ignore’ END
ELSE ‘UNKNOWN’ END
FROM Table
The current challenge in Phocas front end is that you can perform inspections on the measures (for example sales >100 or sales =100), you could do a complex advanced search to “try” to apply these dates but if its a common driver for your reports it will become a large burden on dashboard and favourites, not to mention training staff to remember how to apply such a complex filter. For your sanity and ease of use I strongly reccomend that a back end dimension is created to allow you to either restrict the results at source (i.e. remove all the “ignore” recs from the load) or use the above as a dimension that will allow you to quickly filter. Alternativley using the the above case statement change it to be 1 and 0, you can then use this new calculation to multiply against, for example:
CutOverdimension * SalesAmount as NewMeasure. This means you dont filter on the case staement you use it as a calculation to construct a new measure, this keeps all records present and the measure itself would become the filter.
Hope this helps.
Jon
Thank you gentlemen, I will be spending some time this weekend applying your solutions.
1 Like