Create Quarter series based on one Quarter of a Year
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.
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.
Find more posts tagged with
Sort by:
1 - 3 of
31
@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.
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},"dd/MM/yyyy"),DATE_UNIT_MONTH)"/>
<parameter key="CohortStartYear" value="date_get(date_parse_custom(%{cohortStart},"dd/MM/yyyy"),DATE_UNIT_YEAR)"/>
<parameter key="QuarterStart" value="if((eval(%{CohortStartMonth})>=0 && eval(%{CohortStartMonth})<=2),1, if((eval(%{CohortStartMonth})>=3 && eval(%{CohortStartMonth})<=5),2, if((eval(%{CohortStartMonth})>=6 && eval(%{CohortStartMonth})<=8),3, if((eval(%{CohortStartMonth})>=9 && eval(%{CohortStartMonth})<=11),4,""))))"/>
<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>
@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
Sort by:
1 - 1 of
11
@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
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