How to pull data in Rules Engine based on quarters

  • 18 August 2021
  • 1 reply
  • 14 views

Userlevel 4

In Rules Engine we do not have date filters in terms of quarters as in report builder, here I came with the workaround on how to do that.

 

Let us see some use cases that need Quarter as the date filters:

  1. Pull the number of unique logins happened in last quarter and create CTA if usage drops

  2. Number of meetings CSM had with the customer  in the quarter and draw insights about Engagement

  3. Number of support tickets raised by the customer in the last quarter and decide the support score based on that.

 

To solve these use cases, we should be able to filter based on quarters. But since we do not have that we tend to use last 90 days data but might not give the same data unless you are running the rule on the first day of the current quarter.

So, there is simple work around to pick data quarter-wise:

 

i) To pull the data of previous  quarter:

 

  1. From the data set where pull the date fields, do not apply any data filter. Just pull in the date field into the show section.

  2. In the next transformation, make use of the date formula “Add/Subtract date” under Formula fields. Let's say if you are running on the fifth of every quarter to pull last quarter’s data.

Subtract five days from rule data and that will give the last day of the previous month/quarter.

o3tuDoNNH13wbuxDtejBPWQaxLggy_MFNrP2PhYX99ODrJxW7AKrNac86M5tobKZ36_66dFa64QT48uv6uK7Ih2rfqQkRMcJPAYKXuIspkhsYjq39M_KDozcPCoK3T_tSLMnNtSe


 

EOzjnI79LzhSEL_qsKg6Y_ERBGHKSbjttzJD6PVSUtSYiO7WZjl67QA-VGbikinHCexlRWsGLHx4O5Pyjpk14LW7jV-ZaMpk52QfIDIzrhsUDw5L5Wgik3G7qvk9ftdwq1jv2DS2

4) Now use the formula fields “First day of Calendar quarter” and “Last day of Calendar quarter” in the next transformation from Date formulas.

 

mcfL4TpTWLF3rwao0HiZo_jg5bgosXVRj4TEhqJHAI4M5E6PFkQubKm9Yt3pp6YaUTtWZ2JC_auqKE5vjXQPfWhhsm9VFIYEsXZiWu5C6o77zm3DnIWk8uTse2ZkLUJyDQw29ccy

 

lUzVYvQO41q5o9F8DxZvrlzxF1rVOsUL21y3IhT3300ct8co2SgfD0a-Hchm-oSjkFMnr8eZx3KRiuiyUlBQTalp66N7mbj3DHhRBip3D8XVrp0IgMyiOOP0Gqu7y2fJ77sGJy1k

 

5) Now populate these fields as filters in the next transformation using option to set up filter based field instead of value.


 

0WbyuN1g_O3MBAaiUf7yHB10AUqxREac8Kmm7-NreuojO-Ac-AowkYKz8iVOyEBPp_QeALbewvsuS-Rrq6j-PdZcPhMpDVeOY0YPBUXnK-kIOpD6PkiZ7DVmoDLUkxhN2Qa0r-ad

 

ii) To pull data based on Current Quarter:

 

  1. Similarly pulling data of the current quarter is simpler than pulling previous quarters. You can directly pull First day of current quarter na dLast day of current quarter in one transformation and use theme as filters in next transformation.

 

Select “Rule date” so that it gives the first day of quarter based on the day we are running this rule.l5FmWwAsvAnx2stZbJ5IHFKiFawjrURPhuFbiWzMt79ty7tBH2GElxIAO_K_PvBk6F3qqhIF7YsxXWiUjRNrX7gLx3lrJAAZngJB5gsKytmavEX1QE4ImWtIbgR45yzNNIqD3jhE

 

  1. Apply the filters in the next transformation.

 

 

qtFdp5OB_y4u4rjkxyj34yEhiJUFpWM8iQacqAMi6zzNdHvpnsJkej_5crVr6CpirE9MYOAd8B6QA_QfKKcjrIW_e_8H5fkzg1qMQOpk-iX70FFt_I2eU448bljZlTZ3bd5j9oJX

 

iii) To pull data based on Current and next quarters:

 

  1. Brining the first day of the previous quarter is shown in first case and brining the last day of current quarter is shown in previous case. 

  2. Using those two derived fields, apply the filters to bring data belonging to previous and current quarters.

 

OQrkrquX17_nsmlWOuKTf_FpSQ9vLT7_UqXznNsxiOzWRXU_FN7AwVkry95wRPWBIIYb_TF0ingTipluidLFSyIz6Mhe_rIwUm-2aVB-tS5O8G7bcpsco3P9t5UmX6BuY59xIFGl

 

Similarly you can modify the date formula “add to Rule date “ and by adding the days to rule data, derive the first and last days of next quarters as well.

 

Hope this small work around helps in filtering the data in terms of quarters. Meanwhile our Rules team is also working on bringing direct filters for quarters which may eliminate need of this workarounds.


1 reply

Userlevel 7
Badge +2

@Jyothsna excellent share! thank you!!

Reply