• State Suggested Answer
  • Replies 5 replies
  • Answers 1 answer
  • Subscribers 9 subscribers
  • Views 1486 views
  • Users 0 members are here
Related Discussions
Home » Discussions » AI and Rules

To split up dates based on frequency in Appian

Jansi J
Certified Senior Developer

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: 15th June 2022

End Date: 15th 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 >>1st row: {Start Date: 1st Jun 2022 – 30th Jun 2022}>>2nd row: {Start Date: 1st Jul 2022 – 31st Jul 2022}

Case 2:

Frequency: Quarterly>>then 2 rows >>1st row: {Start Date: 1st Apr 2022 – 30th Jun 2022}>>2nd row: {Start Date: 1st Jul 2022 – 30th Sep 2022}

Case 3:

Frequency: Semi-Annually>>then 2 rows >>1st row: {Start Date: 1st Jan 2022 – 30th Jun 2022}>>2nd row: {Start Date: 1st Jul 2022 – 31st Dec 2022}

Case 4:

Frequency: Annually>>then 1 row>>{Start Date: 1st Jan 2022 – 31st Dec 2022}

Resulting CDT:

Contract Number

Price Grp Id

Frequency

Start Date

End Date

xxx

xxx-1

Monthly

1st Jun 2022

30th Jun 2022

xxx

xxx-1

Monthly

1st Jul 2022

31st Jul 2022

xxx

xxx-2

Quarterly

1st Apr 2022

30th Jun 2022

xxx

xxx-2

Quarterly

1st Jul 2022

30th Sep 2022

xxx

xxx-3

Semi-Annually

1st Jan 2022

30th Jun 2022

xxx

xxx-3

Semi-Annually

1st Jul 2022

31st Dec 2022

xxx

xxx-4

Annually

1st Jan 2022

31st 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.

  Discussion posts and replies are publicly visible

  • 0

    I'm sure this is doable, but I'm not sure exactly how the user-entered dates affect the Start/End calculations.  If they select dates within the same month, does that only make one row for Monthly items?  If they choose April 15 to June 15, does that make 3 monthly rows for each April/May/June, surrounding their date selections?  Should Annual rows have 2 items if they choose Dec 15, 2022 to Jan 15, 2023?  If you could expand how the dates affect each Monthly/Quarterly/Semi-Annually/Annually rows that would be helpful..

  • 0
    Certified Senior Developer
     in reply to Chris

    Thanks for your response Chris!!

    Yes your understanding is correct.

    Example 1:

    Start Date : April 15 2021;End Date : June 15 2021

    Case : If frequency

    1. Monthly >> 3 rows{1st Apr 2021-30th Apr 2021;1st May 2021-31st May 2021;1st Jun 2021-30th Jun 2021}
    2. Quarterly>>1 row{1st Apr 2021-30th Jun 2021}
    3. Semi-Annually>>1 row{1st Jan 2021-30th Jun 2021}
    4. Annually>>1 row{1st Jan 2021- 31st Dec 2021}

    Example 2:

    Start Date :Dec 15 2022; End Date : Jan 15 2023

    Case : If frequency

    1. Monthly>>2 rows {Dec 2022 ; Jan 2023}
    2. Quarterly>>2 rows{Oct-Dec 2022 ; Jan-Mar 2023}
    3. Semi-Annually>>2 rows{Jul-Dec 2022 ; Jan-Jun 2023}
    4. Annually>>2 rows{Jan-Dec 2022 ; Jan-Dec 2023}
  • 0 in reply to Jansi J

    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!

  • 0
    Certified Senior Developer
     in reply to Chris

    Many thanks Chris.

    I will try this out and let you know.

  • 0
    Certified Senior Developer
     in reply to Jansi J

    Hello Chris, thank you so much, it is working as expected, except Semi-Annually frequency, please find the below output which I get using the code you provided,

    • Map
        • contract"xxx"(Text)
          • priceGrp"xxx-1"(Text)
            • frequency"Semi-Annually"(Text)
              • startDate1/1/2022(Date)
                • endDate7/31/2022(Date)
                • Map
                    • contract"xxx"(Text)
                      • priceGrp"xxx-1"(Text)
                        • frequency"Semi-Annually"(Text)
                          • startDate8/1/2022(Date)
                            • endDate12/31/2022(Date)

                          Expected output:

                          • Map
                              • contract"xxx"(Text)
                                • priceGrp"xxx-1"(Text)
                                  • frequency"Semi-Annually"(Text)
                                    • startDate1/1/2022(Date)
                                      • endDate6/30/2022(Date)
                                      • Map
                                          • contract"xxx"(Text)
                                            • priceGrp"xxx-1"(Text)
                                              • frequency"Semi-Annually"(Text)
                                                • startDate7/1/2022(Date)
                                                  • endDate12/31/2022(Date)
                                              © 2024 Appian. All rights reserved.
                                              We want to hear from you! Submit a review on Gartner or G2.