SmartList filter based on math formula

[Anonymous]

How it is possible to define a Smart List filter based on math formula?

Let me introduce an example.
I have a Salesforce accounts and each of them should receive email-report each 7 days.
I create a Campaign which will run on daily basis. Then in the Smart List tab I need to create a special filter which will calculate and choose only those accounts which should receive a report today. Unfortunately, we do not have something like "last report date". We have only when_account_created date. Which means, formula should contain something like "remainder of division ((currect_date-when_created)/7)=0".

How it is possible to defined such a filter?

I have stuck with the custom formula. It doesn't appear as incorrect but meantime it doesn't show in "Smart List -> Filters".
The only possible way the formula field accepted my formula was:

if "When.Created" is "({{system.Date}}-7)or({{system.Date}}-14)or({{system.Date}}-28)or({{system.Date}}-35)or{{system.Date}}-42)or{{system.Date}}-49)or({{system.Date}}-56)" value true.

 I would like to use {{system.Date}} or {{system.DateTime}} on the left hand side of the formula. Thus, it would be interpreted more easier:

if ({{system.Date}} -  {{company.When.Created}})  in (7,14, ..., 56).

Anyway I can't find formula field which can make it possible. Maybe you have some idea(s)?

[Anonymous]

If you want to send a report from Marketo, you should set up a report subscription for the people receiving the report. From the Analytics tab, go to Subscriptions, click New and then New Report Subscription, and set the frequency to weekly. If you don't mind that all the people receive the report on the same day, you can use just one subscription. But if you want different people to receive the report on different days of the week, you will have to set up different subscriptions.

Alternatively, if you want to send an ordinary email (not a report), you can use the filter "Not was sent email" and add the constraint "Date of Activity" and set it to last 6 days. This will ensure that the recipient does not receive the email twice in one week. 

[Anonymous]

Hi Steven,

Thank you for your reply.
I think I will use the second option, because I use an ordinary email containing report link.
Just one more question: since Not was Sent Email will define email used, should I then remove it from Flow section? 

[Anonymous]

Valeriya, 

If your smart list defines those who were not sent the email in the last 6 days, you'll still need to include a flow step to send the email. The difference is you may not need a choice in the flow step to limit who receives the email.

[Anonymous]

Hi Steven,

Additional info : I will work with dynamically changing list of the leads. Everyday some new leads will come up.

So, if I will apply only "Not was Sent Email" rule, it will pick also new leads who have never received an email.
Regarding this I need to put some new restriction. I'm planning to use lead.first listind date filter to check whether this new lead had been activated in CRM 7 days ago (which also need some formula be used). 

Thus, ("Not was Sent Email" AND lead.first listind date in past 7 days) will give me only new clients
Thus, ("Not was Sent Email" OR lead.first listind date in past 7 days) will give me every client I need + new clients who appeared during 7 days (today, yesterday,..). 

Could you, please, advise?

Regards,
Valeriya

[Anonymous]

Valeriya, I haven't used this filter lead.first listin date.  I believe your first choice will work, but you should test to be certain.

[Diederik_Marte4]

If anyone has challenges with formulas and math, please check: CALCITNOW - Field formula calculations – Marketo LaunchPoint