Create Quarter series based on one Quarter of a Year

achint_kumar

Hi All,

I am trying to make a process very dynamic in terms of creating multiple series of quarters based on a quarter input

Example:
I have a date for eg: 01/04/2019.
The above date falls in Q2, so i need to create 4 macros in Generate macro which will be 1 year before that is 4 quarters.
That is Q1_19, Q4_18, Q3_18, Q2_18.

Hence the 4 macros will be having values as:
Macro 1:  Q1_19
Macro 2:  Q4_18
Macro 3:  Q3_18
Macro 4:  Q2_18

Similarly for another Quarter, i need to create 4 new quarters for the next year.
For eg: Date = 30/06/2019 falls in Q2

Hence i need to create 4 macros will values:
Macro 1:  Q3_19
Macro 2:  Q4_19
Macro 3:  Q1_20
Macro 4:  Q2_20

Do we have some operator where we can achieve this dynamically? Thank you.

Marco_Barradas

@achint_kumar then you can create a column with adjust date  in order to get a date 4 quarters before and another for a date 4 quarters ahead. Then extract the macro those dates and use it on a filter example and a loop

Marco_Barradas

Hi @achint_kumar you could copy your attribute and then use the Date to Numerical and some concatenation.
you could also generate and attribute and through concatenation and date part extractions get to it.
For some reason Date to Nominal doesn't accept the q argument to extract the Quarter of a date.
@sgenzer could you help us with the Date to Nominal operator?

This is the example that could get you started

<?xml version="1.0" encoding="UTF-8"?><process version="9.6.000">
  <context>
    <input/>
    <output/>
    <macros/>
  </context>
  <operator activated="true" class="process" compatibility="9.6.000" expanded="true" name="Process">
    <parameter key="logverbosity" value="init"/>
    <parameter key="random_seed" value="2001"/>
    <parameter key="send_mail" value="never"/>
    <parameter key="notification_email" value=""/>
    <parameter key="process_duration_for_mail" value="30"/>
    <parameter key="encoding" value="SYSTEM"/>
    <process expanded="true">
      <operator activated="true" class="utility:create_exampleset" compatibility="9.6.000" expanded="true" height="68" name="Create ExampleSet" width="90" x="112" y="34">
        <parameter key="generator_type" value="date series"/>
        <parameter key="number_of_examples" value="100"/>
        <parameter key="use_stepsize" value="false"/>
        <list key="function_descriptions"/>
        <parameter key="add_id_attribute" value="false"/>
        <list key="numeric_series_configuration"/>
        <list key="date_series_configuration">
          <parameter key="Date" value="2019-01-01.2020-12-31"/>
        </list>
        <list key="date_series_configuration (interval)"/>
        <parameter key="date_format" value="yyyy-MM-dd"/>
        <parameter key="time_zone" value="SYSTEM"/>
        <parameter key="column_separator" value=","/>
        <parameter key="parse_all_as_nominal" value="false"/>
        <parameter key="decimal_point_character" value="."/>
        <parameter key="trim_attribute_names" value="true"/>
      </operator>
      <operator activated="true" class="generate_copy" compatibility="9.6.000" expanded="true" height="82" name="Generate Copy" width="90" x="246" y="34">
        <parameter key="attribute_name" value="Date"/>
        <parameter key="new_name" value="Quarter"/>
      </operator>
      <operator activated="true" class="generate_copy" compatibility="9.6.000" expanded="true" height="82" name="Generate Copy (2)" width="90" x="380" y="34">
        <parameter key="attribute_name" value="Date"/>
        <parameter key="new_name" value="Year"/>
      </operator>
      <operator activated="true" class="multiply" compatibility="9.6.000" expanded="true" height="103" name="Multiply" width="90" x="514" y="34"/>
      <operator activated="true" class="generate_attributes" compatibility="9.6.000" expanded="true" height="82" name="Second_Option" width="90" x="648" y="238">
        <list key="function_descriptions">
          <parameter key="Quarter" value="concat(&quot;Q&quot;,str(floor((date_get(Date,DATE_UNIT_MONTH)/3)+1)),&quot;_&quot;,str(date_get(Date,DATE_UNIT_YEAR)))"/>
        </list>
        <parameter key="keep_all" value="true"/>
      </operator>
      <operator activated="true" class="date_to_numerical" compatibility="9.6.000" expanded="true" height="82" name="Date to Numerical" width="90" x="648" y="34">
        <parameter key="attribute_name" value="Quarter"/>
        <parameter key="time_unit" value="quarter"/>
        <parameter key="millisecond_relative_to" value="second"/>
        <parameter key="second_relative_to" value="minute"/>
        <parameter key="minute_relative_to" value="hour"/>
        <parameter key="hour_relative_to" value="day"/>
        <parameter key="day_relative_to" value="month"/>
        <parameter key="week_relative_to" value="year"/>
        <parameter key="month_relative_to" value="year"/>
        <parameter key="quarter_relative_to" value="year"/>
        <parameter key="half_year_relative_to" value="year"/>
        <parameter key="year_relative_to" value="era"/>
        <parameter key="keep_old_attribute" value="false"/>
      </operator>
      <operator activated="true" class="date_to_numerical" compatibility="9.6.000" expanded="true" height="82" name="Date to Numerical (2)" width="90" x="782" y="34">
        <parameter key="attribute_name" value="Year"/>
        <parameter key="time_unit" value="year"/>
        <parameter key="millisecond_relative_to" value="second"/>
        <parameter key="second_relative_to" value="minute"/>
        <parameter key="minute_relative_to" value="hour"/>
        <parameter key="hour_relative_to" value="day"/>
        <parameter key="day_relative_to" value="month"/>
        <parameter key="week_relative_to" value="year"/>
        <parameter key="month_relative_to" value="year"/>
        <parameter key="quarter_relative_to" value="year"/>
        <parameter key="half_year_relative_to" value="year"/>
        <parameter key="year_relative_to" value="era"/>
        <parameter key="keep_old_attribute" value="false"/>
      </operator>
      <operator activated="true" class="generate_concatenation" compatibility="9.6.000" expanded="true" height="82" name="Generate Concatenation" width="90" x="916" y="34">
        <parameter key="first_attribute" value="Quarter"/>
        <parameter key="second_attribute" value="Year"/>
        <parameter key="separator" value="_"/>
        <parameter key="trim_values" value="false"/>
      </operator>
      <operator activated="true" class="replace" compatibility="9.6.000" expanded="true" height="82" name="Option_1" width="90" x="1050" y="34">
        <parameter key="attribute_filter_type" value="single"/>
        <parameter key="attribute" value="Quarter_Year"/>
        <parameter key="attributes" value=""/>
        <parameter key="use_except_expression" value="false"/>
        <parameter key="value_type" value="nominal"/>
        <parameter key="use_value_type_exception" value="false"/>
        <parameter key="except_value_type" value="file_path"/>
        <parameter key="block_type" value="single_value"/>
        <parameter key="use_block_type_exception" value="false"/>
        <parameter key="except_block_type" value="single_value"/>
        <parameter key="invert_selection" value="false"/>
        <parameter key="include_special_attributes" value="false"/>
        <parameter key="replace_what" value="\.0"/>
      </operator>
      <connect from_op="Create ExampleSet" from_port="output" to_op="Generate Copy" to_port="example set input"/>
      <connect from_op="Generate Copy" from_port="example set output" to_op="Generate Copy (2)" to_port="example set input"/>
      <connect from_op="Generate Copy (2)" from_port="example set output" to_op="Multiply" to_port="input"/>
      <connect from_op="Multiply" from_port="output 1" to_op="Date to Numerical" to_port="example set input"/>
      <connect from_op="Multiply" from_port="output 2" to_op="Second_Option" to_port="example set input"/>
      <connect from_op="Second_Option" from_port="example set output" to_port="result 2"/>
      <connect from_op="Date to Numerical" from_port="example set output" to_op="Date to Numerical (2)" to_port="example set input"/>
      <connect from_op="Date to Numerical (2)" from_port="example set output" to_op="Generate Concatenation" to_port="example set input"/>
      <connect from_op="Generate Concatenation" from_port="example set output" to_op="Option_1" to_port="example set input"/>
      <connect from_op="Option_1" from_port="example set output" to_port="result 1"/>
      <portSpacing port="source_input 1" spacing="0"/>
      <portSpacing port="sink_result 1" spacing="0"/>
      <portSpacing port="sink_result 2" spacing="0"/>
      <portSpacing port="sink_result 3" spacing="0"/>
    </process>
  </operator>
</process>

achint_kumar

@MarcoBarradas: Thanks for your reply Marco, i have already extracted the Quarter and Year out of the Date, my worry is how can i create a series of 4 quarters before and after a date as per my post? 
I may also have to chek 8 quaters or 12 quarters before/after the date. SO it has to be very dynamic.

<?xml version="1.0" encoding="UTF-8"?><process version="9.5.001">

  <operator activated="true" class="read_excel" compatibility="9.5.001" expanded="true" height="68" name="Read Excel" width="90" x="45" y="136">

    <parameter key="excel_file" value="C:\Users\Achint.Kumar\Desktop\LAAD\KPI 1 - Persistency\27-1-2020 Requirement\Input_Data - Persistency.xlsx"/>

    <parameter key="sheet_selection" value="sheet number"/>

    <parameter key="sheet_number" value="2"/>

    <parameter key="imported_cell_range" value="A1"/>

    <parameter key="encoding" value="SYSTEM"/>

    <parameter key="first_row_as_names" value="true"/>

    <list key="annotations"/>

    <parameter key="date_format" value="dd/MM/yyyy"/>

    <parameter key="time_zone" value="SYSTEM"/>

    <parameter key="locale" value="English (United States)"/>

    <parameter key="read_all_values_as_polynominal" value="false"/>

    <list key="data_set_meta_data_information">

      <parameter key="0" value="Variable.true.polynominal.attribute"/>

      <parameter key="1" value="Intermediate table avaialble.true.polynominal.attribute"/>

      <parameter key="2" value="Server.true.polynominal.attribute"/>

      <parameter key="3" value="Database.true.polynominal.attribute"/>

      <parameter key="4" value="Table name.true.polynominal.attribute"/>

      <parameter key="5" value="Latest data available.true.integer.attribute"/>

      <parameter key="6" value="Cohort period.true.date.attribute"/>

      <parameter key="7" value="Cohort definition.true.integer.attribute"/>

      <parameter key="8" value="Cohort definition based on.true.integer.attribute"/>

      <parameter key="9" value="Look back period.true.integer.attribute"/>

      <parameter key="10" value="Analysis period.true.integer.attribute"/>

      <parameter key="11" value="Grace Period.true.integer.attribute"/>

      <parameter key="12" value="Minimum days of persistency.true.integer.attribute"/>

      <parameter key="13" value="Days of Supply.true.integer.attribute"/>

      <parameter key="14" value="Product Market Definition.true.integer.attribute"/>

      <parameter key="15" value="Focus Product.true.polynominal.attribute"/>

      <parameter key="16" value="Look back patient stability.true.integer.attribute"/>

      <parameter key="17" value="Look forward patient stability.true.integer.attribute"/>

      <parameter key="18" value="QC Validation.true.polynominal.attribute"/>

      <parameter key="19" value="Output path.true.polynominal.attribute"/>

    </list>

    <parameter key="read_not_matching_values_as_missings" value="false"/>

    <parameter key="datamanagement" value="double_array"/>

    <parameter key="data_management" value="auto"/>

    <description align="center" color="transparent" colored="false" width="126">Read Business Requirements</description>

  </operator>

</process>

<?xml version="1.0" encoding="UTF-8"?><process version="9.5.001">

  <operator activated="true" class="date_to_nominal" compatibility="9.5.001" expanded="true" height="82" name="Date to Nominal" width="90" x="179" y="136">

    <parameter key="attribute_name" value="Cohort period"/>

    <parameter key="date_format" value="dd/MM/yyyy"/>

    <parameter key="time_zone" value="SYSTEM"/>

    <parameter key="locale" value="English (United States)"/>

    <parameter key="keep_old_attribute" value="false"/>

    <description align="center" color="transparent" colored="false" width="126">Change date format to Nominal (dd/MM/yyyy)</description>

  </operator>

</process>

<?xml version="1.0" encoding="UTF-8"?><process version="9.5.001">

  <operator activated="true" class="extract_macro" compatibility="9.5.001" expanded="true" height="68" name="Cohort Start" width="90" x="514" y="340">

    <parameter key="macro" value="cohortStart"/>

    <parameter key="macro_type" value="data_value"/>

    <parameter key="statistics" value="average"/>

    <parameter key="attribute_name" value="Cohort period"/>

    <parameter key="example_index" value="1"/>

    <list key="additional_macros"/>

  </operator>

</process>

<?xml version="1.0" encoding="UTF-8"?><process version="9.5.001">

  <operator activated="true" class="generate_macro" compatibility="9.5.001" expanded="true" height="82" name="Generate Macro" width="90" x="648" y="340">

    <list key="function_descriptions">

      <parameter key="CohortStartMonth" value="date_get(date_parse_custom(%{cohortStart},&quot;dd/MM/yyyy&quot;),DATE_UNIT_MONTH)"/>

      <parameter key="CohortStartYear" value="date_get(date_parse_custom(%{cohortStart},&quot;dd/MM/yyyy&quot;),DATE_UNIT_YEAR)"/>

      <parameter key="QuarterStart" value="if((eval(%{CohortStartMonth})&gt;=0 &amp;&amp; eval(%{CohortStartMonth})&lt;=2),1,&#10;if((eval(%{CohortStartMonth})&gt;=3 &amp;&amp; eval(%{CohortStartMonth})&lt;=5),2,&#10;if((eval(%{CohortStartMonth})&gt;=6 &amp;&amp; eval(%{CohortStartMonth})&lt;=8),3,&#10;if((eval(%{CohortStartMonth})&gt;=9 &amp;&amp; eval(%{CohortStartMonth})&lt;=11),4,&quot;&quot;))))"/>

      <parameter key="YearStart" value="%{CohortStartYear}"/>

      <parameter key="YearStartYY" value="parse(cut(%{YearStart},2,2))"/>

    </list>

  </operator>

</process>

<?xml version="1.0" encoding="UTF-8"?><process version="9.5.001">

  <operator activated="true" class="generate_macro" compatibility="9.5.001" expanded="true" height="82" name="Generate Macro (3)" width="90" x="782" y="340">

    <list key="function_descriptions">

      <parameter key="Q1S" value="if((eval(%{QuarterStart})-1) != 0,(eval(%{QuarterStart})-1),4)"/>

      <parameter key="Q2S" value="if((eval(%{Q1S})-1) != 0,(eval(%{Q1S})-1),4)"/>

      <parameter key="Q3S" value="if((eval(%{Q2S})-1) != 0,(eval(%{Q2S})-1),4)"/>

      <parameter key="Q4S" value="if((eval(%{Q3S})-1) != 0,(eval(%{Q3S})-1),4)"/>

    </list>

  </operator>

</process>

<?xml version="1.0" encoding="UTF-8"?><process version="9.5.001">

  <operator activated="true" class="generate_macro" compatibility="9.5.001" expanded="true" height="82" name="Generate Macro (4)" width="90" x="916" y="340">

    <list key="function_descriptions">

      <parameter key="Y1S" value="if(eval(%{Q1S}) != 4,eval(%{YearStartYY}),(eval(%{YearStartYY})-1))"/>

      <parameter key="Y2S" value="if(eval(%{Q2S}) != 4 ,eval(%{YearStartYY}),(eval(%{YearStartYY})-1))"/>

      <parameter key="Y3S" value="if(eval(%{Q3S}) != 4,eval(%{YearStartYY}),(eval(%{YearStartYY})-1))"/>

      <parameter key="Y4S" value="if(eval(%{Q4S}) != 4,eval(%{YearStartYY}),(eval(%{YearStartYY})-1))"/>

    </list>

  </operator>

</process>

<?xml version="1.0" encoding="UTF-8"?><process version="9.5.001">

  <operator activated="true" breakpoints="after" class="generate_attributes" compatibility="9.5.001" expanded="true" height="82" name="Generate Attributes (3)" width="90" x="1050" y="340">

    <list key="function_descriptions">

      <parameter key="Q1SS" value="%{Q1S}"/>

      <parameter key="Q2SS" value="%{Q2S}"/>

      <parameter key="Q3SS" value="%{Q3S}"/>

      <parameter key="Q4SS" value="%{Q4S}"/>

      <parameter key="Y1SS" value="%{Y1S}"/>

      <parameter key="Y2SS" value="%{Y2S}"/>

      <parameter key="Y3SS" value="%{Y3S}"/>

      <parameter key="Y4SS" value="%{Y4S}"/>

    </list>

    <parameter key="keep_all" value="true"/>

  </operator>

</process>

<?xml version="1.0" encoding="UTF-8"?><process version="9.5.001">

  <operator activated="true" class="multiply" compatibility="9.5.001" expanded="true" height="208" name="Multiply (23)" width="90" x="313" y="136"/>

</process>

Marco_Barradas

@achint_kumar then you can create a column with adjust date  in order to get a date 4 quarters before and another for a date 4 quarters ahead. Then extract the macro those dates and use it on a filter example and a loop