Create Quarter series based on one Quarter of a Year

achint_kumar · April 2020

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.

MarcoBarradas · April 2020

@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

MarcoBarradas · April 2020

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 · April 2020

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