Question

To split up dates based on frequency in Appian

Forum|Forum|3 years ago
August 9, 2022
5 replies
0 views

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

Case 2:

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

Case 3:

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

Case 4:

Frequency: Annually>>then 1 row>>{Start Date: 1^st Jan 2022 – 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.

csteward

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

jansij055677Author

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

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

Example 2:

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

Case : If frequency

Monthly>>2 rows {Dec 2022 ; Jan 2023}
Quarterly>>2 rows{Oct-Dec 2022 ; Jan-Mar 2023}
Semi-Annually>>2 rows{Jul-Dec 2022 ; Jan-Jun 2023}
Annually>>2 rows{Jan-Dec 2022 ; Jan-Dec 2023}

csteward

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


Let me know how that works out!



    

                                        
                                                        
            
        
    



                                    Powered by Gainsight
Terms & ConditionsAccessibility statement

            
        


        
            
    
        
    
        
                                        
            
                

    
    
      
    




            Sign up
    

    Already have an account? Login
    



    
            Login with SSO 
    
    
    
    
    
    
            
            Login with OpenIdConnect
        
    
    
    
    
    
    
    
    



            
        
                            
            
                
    
        
    

            Login to the community
    
            No account yet? Create an account
    
    
            Login with SSO 
    
    
    
    
    
    
            
            Login with OpenIdConnect
        
    
    
    
    
    
    
    
    
                
        
                
        
            Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.



  
      
  

      
    
        
                        
                            Enter your e-mail address
            
            
                                
        
        
                        

        
    

  
      
                Back to overview
        
            


        
    


        
                


        

        
                                                        

                                        
    
                    
  

    

      
        
        
        
          
            
          
          
            
          
          
            
          
          
            
          
          
            
          
        
      

      
        
          Community
          
            FAQs
            Content Agreement
            Contact Appian
          
        
        
          Legal
          
            Terms & Conditions
            Privacy Policy
            
          
        
      

    

  

  
    Copyright © 2026 Appian. All rights reserved.