To split up dates based on frequency in Appian

Question

Hi All, 
 Assume like the user have entered the below start date and end date and also, they have selected some contracts and price groups based on the date logic. 
 Start Date: 15 th June 2022 
 End Date: 15 th July 2022 
 Now I have a table like below with all the selected data,

Contract number

Price grp id

Frequency

xxx

xxx-1

Monthly

xxx

xxx-2

Quarterly

xxx

xxx-3

Semi-Annually

xxx

xxx-4

Annually

My scenario is like I have to check the frequency from above table(cdt) and split it into multiple rows based on the date range (Start Date & End Date) and update in another table(cdt),please refer the resulting expectation, 
 Case 1: 
 Frequency: Monthly >>then 2 rows >>1 st row: {Start Date: 1 st Jun 2022 &ndash; 30 th Jun 2022}>>2 nd row: {Start Date: 1 st Jul 2022 &ndash; 31 st Jul 2022} 
 Case 2: 
 Frequency: Quarterly >>then 2 rows >>1 st row: {Start Date: 1 st Apr 2022 &ndash; 30 th Jun 2022}>>2 nd row: {Start Date: 1 st Jul 2022 &ndash; 30 th Sep 2022} 
 Case 3: 
 Frequency: Semi-Annually >>then 2 rows >>1 st row: {Start Date: 1 st Jan 2022 &ndash; 30 th Jun 2022}>>2 nd row: {Start Date: 1 st Jul 2022 &ndash; 31 st Dec 2022} 
 Case 4: 
 Frequency: Annually >>then 1 row>>{Start Date: 1 st Jan 2022 &ndash; 31 st Dec 2022} 
 
 Resulting CDT:

Contract Number

Price Grp Id

Frequency

Start Date

End Date

xxx

xxx-1

Monthly

1 st Jun 2022

30 th Jun 2022

xxx

xxx-1

Monthly

1 st Jul 2022

31 st Jul 2022

xxx

xxx-2

Quarterly

1 st Apr 2022

30 th Jun 2022

xxx

xxx-2

Quarterly

1 st Jul 2022

30 th Sep 2022

xxx

xxx-3

Semi-Annually

1 st Jan 2022

30 th Jun 2022

xxx

xxx-3

Semi-Annually

1 st Jul 2022

31 st Dec 2022

xxx

xxx-4

Annually

1 st Jan 2022

31 st Dec 2022

Is it possible to do this kind of split up for dates based on frequency in Appian Expression Rule. Could you please help me. 
 Thanks in Advance.

Chris · Answer

Try this out for size. 4 helper rules required: 
 rule!chris_test_frequency_monthly(data (Any Type), startDate (Date), endDate (Date)) 
 a!localVariables(
 local!yearDiff: year(ri!endDate) - year(ri!startDate),
 local!monthDiff: (local!yearDiff) * 12 + month(ri!endDate) - month(ri!startDate) + 1,
 local!startDate: date(year(ri!startDate),month(ri!startDate),1),
 
 a!forEach(
 items: 1+enumerate(local!monthDiff),
 expression: a!map(
 contract: ri!data.contract,
 priceGrp: ri!data.priceGrp,
 frequency: ri!data.frequency,
 startDate: edate(local!startDate,fv!index-1),
 endDate: eomonth(local!startDate,fv!index-1)
 )
 )
) 
 rule!chris_test_frequency_quarterly(data (Any Type), startDate (Date), endDate (Date)) 
 a!localVariables(
 local!quartersStart: {
 date(year(ri!startDate),1,1),
 date(year(ri!startDate),4,1),
 date(year(ri!startDate),7,1),
 date(year(ri!startDate),10,1)
 },
 local!startQuarter: ceiling(month(ri!startDate)/3,1),
 local!endQuarter: ceiling(month(ri!endDate)/3,1),
 local!years: year(ri!endDate)-year(ri!startDate),
 local!quarters: (local!years) * 4 + local!endQuarter - local!startQuarter + 1,
 local!outputDateStart: index(local!quartersStart,local!startQuarter,null),
 
 a!forEach(
 items: 1+enumerate(local!quarters),
 expression: a!map(
 contract: ri!data.contract,
 priceGrp: ri!data.priceGrp,
 frequency: ri!data.frequency,
 startDate: edate(local!outputDateStart,(fv!index-1)*3),
 endDate: eomonth(local!outputDateStart,((fv!index-1)*3)+2)
 )
 )
) 
 rule!chris_test_frequency_semi_annually(data (Any Type), startDate (Date), endDate (Date)) 
 a!localVariables(
 local!periodStart: {
 date(year(ri!startDate),1,1),
 date(year(ri!startDate),6,1)
 },
 local!startPeriod: ceiling(month(ri!startDate)/6,1),
 local!endPeriod: ceiling(month(ri!endDate)/6,1),
 local!years: year(ri!endDate)-year(ri!startDate),
 local!periods: (local!years) * 2 + local!endPeriod - local!startPeriod + 1,
 local!outputDateStart: index(local!periodStart,local!startPeriod,null),

 a!forEach(
 items: 1+enumerate(local!periods),
 expression: a!map(
 contract: ri!data.contract,
 priceGrp: ri!data.priceGrp,
 frequency: ri!data.frequency,
 startDate: edate(local!outputDateStart,(fv!index-1)*6+(mod(fv!index+1,2))),
 endDate: eomonth(local!outputDateStart,(fv!index*6)-(mod(fv!index+1,2)))
 )
 )
) 
 rule!chris_test_frequency_annually(data (Any Type), startDate (Date), endDate (Date)) 
 a!localVariables(
 local!years: year(ri!endDate)-year(ri!startDate)+1,
 local!outputDateStart: date(year(ri!startDate),1,1),

 a!forEach(
 items: 1+enumerate(local!years),
 expression: a!map(
 contract: ri!data.contract,
 priceGrp: ri!data.priceGrp,
 frequency: ri!data.frequency,
 startDate: edate(local!outputDateStart,(fv!index-1)*12),
 endDate: eomonth(local!outputDateStart,(fv!index*12-1))
 )
 )
) 
 This is your main interface - starting data is shown up top, enter dates below and click Calculate. This will generate a grid below with the result. Essentially, helper rules are called for each frequency in the initial data set, they decide how many of those specific period types the dates cover and generate one row with the corresponding start/end dates per period. 
 a!localVariables(
 local!startDate,
 local!endDate,
 local!data: {
 a!map(contract: "xxx", priceGrp: "xxx-1", frequency: "Monthly"),
 a!map(contract: "xxx", priceGrp: "xxx-2", frequency: "Quarterly"),
 a!map(contract: "xxx", priceGrp: "xxx-3", frequency: "Semi-Annually"),
 a!map(contract: "xxx", priceGrp: "xxx-4", frequency: "Annually")
 },
 local!result,

 {
 a!gridField(
 label: "Main Data",
 data: local!data,
 columns: {
 a!gridColumn(
 label: "Contract Number",
 value: fv!row.contract
 ),
 a!gridColumn(
 label: "Price GRP ID",
 value: fv!row.priceGrp
 ),
 a!gridColumn(
 label: "Frequency",
 value: fv!row.frequency
 )
 }
 ),
 a!columnsLayout(
 columns: {
 a!columnLayout(),
 a!columnLayout(
 contents: {
 a!dateField(
 label: "Start Date",
 value: local!startDate,
 saveInto: local!startDate
 )
 }
 ),
 a!columnLayout(
 contents: {
 a!dateField(
 label: "End Date",
 value: local!endDate,
 saveInto: local!endDate,
 validations: if(
 or(
 rule!APN_isEmpty(local!startDate),
 rule!APN_isEmpty(local!endDate),
 local!endDate>=local!startDate
 ),
 null,
 "End date must be greater than start date"
 )
 )
 }
 ),
 a!columnLayout()
 }
 ),
 a!buttonArrayLayout(
 align: "CENTER",
 buttons: {
 a!buttonWidget(
 label: "Calculate",
 disabled: or(
 rule!APN_isEmpty(local!startDate),
 rule!APN_isEmpty(local!endDate),
 local!endDate<local!startDate
 ),
 saveInto: {
 a!save(
 local!result,
 a!flatten(
 a!forEach(
 items: local!data,
 expression: {
 choose(
 wherecontains(fv!item.frequency,{"Monthly","Quarterly","Semi-Annually","Annually"}),
 rule!chris_test_frequency_monthly(data: fv!item, startDate: local!startDate, endDate: local!endDate),
 rule!chris_test_frequency_quarterly(data: fv!item, startDate: local!startDate, endDate: local!endDate),
 rule!chris_test_frequency_semi_annually(data: fv!item, startDate: local!startDate, endDate: local!endDate),
 rule!chris_test_frequency_annually(data: fv!item, startDate: local!startDate, endDate: local!endDate)
 )
 }
 )
 )
 )
 }
 )
 }
 ),
 
 a!gridField(
 label: "Updated Data",
 data: local!result,
 showWhen: not(rule!APN_isEmpty(local!result)),
 columns: {
 a!gridColumn(
 label: "Contract Number",
 value: fv!row.contract
 ),
 a!gridColumn(
 label: "Price GRP ID",
 value: fv!row.priceGrp
 ),
 a!gridColumn(
 label: "Frequency",
 value: fv!row.frequency
 ),
 a!gridColumn(
 label: "Start Date",
 value: fv!row.startDate
 ),
 a!gridColumn(
 label: "End Date",
 value: fv!row.endDate
 )
 }
 ),
 }
) 
 Let me know how that works out!