How to do subtraction of fields in the same column. The KPI contains 6-7 columns as filters

Achint

Hi All,

I have a query in building a KPI. 

The instruction is given in the attached excel file(I am using two dummy columns here, but actually i have 6-7 columns).

In case of any queries please let me know. Thank you very much.

Best regards,
Achint

Telcontar120

Lag or Differentiate operator from Time Series should do what you are looking for.  Check out the sample processes in the help menu.

lionelderkrikor

Hi @Achint,

I propose 2 solutions from your initial example set : 


Solution 1 : 

This solution performs exactly what you want to do, but uses a Python script.
So you have to : 
 - Install Python on your computer.
 - Install in RapidMiner the Python Scripting extension from the Marketplace.
The output example set looks like that : 



Solution 2 : 
This solution displays the results in a other way (as in your Excel file), but use only RapidMiner's native operators (no Python script).
 A "default_count" attribute is created and then the example set is pivoted (via the Pivot operator).
The output example set looks like that : 



The process : 

<?xml version="1.0" encoding="UTF-8"?><process version="9.4.000-BETA">
  <context>
    <input/>
    <output/>
    <macros/>
  </context>
  <operator activated="true" class="process" compatibility="9.4.000-BETA" 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.4.000-BETA" expanded="true" height="68" name="Create ExampleSet" width="90" x="179" y="85">
        <parameter key="generator_type" value="comma separated text"/>
        <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"/>
        <list key="date_series_configuration (interval)"/>
        <parameter key="date_format" value="yyyy-MM-dd HH:mm:ss"/>
        <parameter key="time_zone" value="SYSTEM"/>
        <parameter key="input_csv_text" value="Country, Gender,Q1&#10;Australia,M,Increase&#10;Australia,F,Increase,&#10;Australia,M,Increase&#10;Australia,M,Decrease&#10;India,M,Decrease&#10;India,F,Increase&#10;India,M,Decrease&#10;South Africa,F,Decrease&#10;UK,F,Increase&#10;"/>
        <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="multiply" compatibility="9.4.000-BETA" expanded="true" height="103" name="Multiply" width="90" x="246" y="187"/>
      <operator activated="true" class="generate_attributes" compatibility="9.4.000-BETA" expanded="true" height="82" name="Generate Attributes" width="90" x="380" y="238">
        <list key="function_descriptions">
          <parameter key="default_count" value="1"/>
        </list>
        <parameter key="keep_all" value="true"/>
      </operator>
      <operator activated="true" class="python_scripting:execute_python" compatibility="9.3.000" expanded="true" height="103" name="Execute Python" width="90" x="514" y="85">
        <parameter key="script" value="import pandas as pd&#10;&#10;# rm_main is a mandatory function, &#10;# the number of arguments has to be the number of input ports (can be none)&#10;def rm_main(data):&#10;&#10;  df1 = pd.DataFrame(data.groupby(['Country','Gender','Q1'])['Q1']\&#10;                   .count()).rename(columns={'Q1':'CountQ1'}).reset_index()&#10;                   &#10;  df2 =pd.pivot_table(data,index = ['Country','Gender'],&#10;                  columns = 'Q1',&#10;                  values = 'Q1',&#10;                  aggfunc = lambda x: len(x),&#10;                  margins_name = 'Q1').fillna(0).reset_index().copy()&#10;&#10;  df2 = df2.assign(CountQ1 = df2.Increase - df2.Decrease,&#10;           Q1='Net',&#10;           Net = df2.apply(lambda x: f'({int(x.Increase)}-{int(x.Decrease)})',axis=1))\&#10;  .drop(['Increase','Decrease'],axis=1)&#10;  &#10;  df3 = pd.concat([df1,df2]).sort_values(['Country','Gender'],ascending=(1,0))[['Country','Gender','Q1','CountQ1','Net']]&#10;&#10;&#10;    # connect 2 output ports to see the results&#10;  return df3"/>
        <parameter key="notebook_cell_tag_filter" value=""/>
        <parameter key="use_default_python" value="true"/>
        <parameter key="package_manager" value="conda (anaconda)"/>
      </operator>
      <operator activated="true" class="blending:pivot" compatibility="9.4.000-BETA" expanded="true" height="82" name="Pivot" width="90" x="514" y="238">
        <parameter key="group_by_attributes" value="Country|Gender"/>
        <parameter key="column_grouping_attribute" value="Q1"/>
        <list key="aggregation_attributes">
          <parameter key="default_count" value="count"/>
        </list>
        <parameter key="use_default_aggregation" value="false"/>
        <parameter key="default_aggregation_function" value="first"/>
      </operator>
      <operator activated="true" class="replace_missing_values" compatibility="9.4.000-BETA" expanded="true" height="103" name="Replace Missing Values" width="90" x="648" y="238">
        <parameter key="return_preprocessing_model" value="false"/>
        <parameter key="create_view" value="false"/>
        <parameter key="attribute_filter_type" value="all"/>
        <parameter key="attribute" value=""/>
        <parameter key="attributes" value=""/>
        <parameter key="use_except_expression" value="false"/>
        <parameter key="value_type" value="attribute_value"/>
        <parameter key="use_value_type_exception" value="false"/>
        <parameter key="except_value_type" value="time"/>
        <parameter key="block_type" value="attribute_block"/>
        <parameter key="use_block_type_exception" value="false"/>
        <parameter key="except_block_type" value="value_matrix_row_start"/>
        <parameter key="invert_selection" value="false"/>
        <parameter key="include_special_attributes" value="false"/>
        <parameter key="default" value="zero"/>
        <list key="columns"/>
      </operator>
      <operator activated="true" class="generate_attributes" compatibility="9.4.000-BETA" expanded="true" height="82" name="Generate Attributes (2)" width="90" x="782" y="238">
        <list key="function_descriptions">
          <parameter key="Net" value="[count(default_count)_Increase]-[count(default_count)_Decrease]"/>
        </list>
        <parameter key="keep_all" value="true"/>
      </operator>
      <operator activated="true" class="rename" compatibility="9.4.000-BETA" expanded="true" height="82" name="Rename" width="90" x="916" y="238">
        <parameter key="old_name" value="count(default_count)_Decrease"/>
        <parameter key="new_name" value="Decrease"/>
        <list key="rename_additional_attributes">
          <parameter key="count(default_count)_Increase" value="Increase"/>
        </list>
      </operator>
      <operator activated="true" class="order_attributes" compatibility="9.4.000-BETA" expanded="true" height="82" name="Reorder Attributes" width="90" x="1050" y="238">
        <parameter key="sort_mode" value="user specified"/>
        <parameter key="attribute_ordering" value="Country|Gender|count(default_count)_Infinity|count(default_count)_-Infinity|Net"/>
        <parameter key="use_regular_expressions" value="false"/>
        <parameter key="handle_unmatched" value="append"/>
        <parameter key="sort_direction" value="descending"/>
      </operator>
      <connect from_op="Create ExampleSet" from_port="output" to_op="Multiply" to_port="input"/>
      <connect from_op="Multiply" from_port="output 1" to_op="Execute Python" to_port="input 1"/>
      <connect from_op="Multiply" from_port="output 2" to_op="Generate Attributes" to_port="example set input"/>
      <connect from_op="Generate Attributes" from_port="example set output" to_op="Pivot" to_port="input"/>
      <connect from_op="Execute Python" from_port="output 1" to_port="result 1"/>
      <connect from_op="Pivot" from_port="output" to_op="Replace Missing Values" to_port="example set input"/>
      <connect from_op="Replace Missing Values" from_port="example set output" to_op="Generate Attributes (2)" to_port="example set input"/>
      <connect from_op="Generate Attributes (2)" from_port="example set output" to_op="Rename" to_port="example set input"/>
      <connect from_op="Rename" from_port="example set output" to_op="Reorder Attributes" to_port="example set input"/>
      <connect from_op="Reorder Attributes" from_port="example set output" to_port="result 2"/>
      <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>

Hope this helps,

Regards,

Lionel