Sum OVER specific values from rows

[Tymoteusz Grzybowski]

Hi,

Please help me with this issue. How can I create new value row from already existed one in data table

Example:

I have Sales of 4 products reported per day. I would like to create new product from specific 2 from existing data.

Please check attachment to see the real data table.

Thanks for any help!

Greetings from Poland,

tymek

[Khushboo Rabadia]

Can you please provide the sample excel whose screenshot you have attached so it is easier to create calculated column. Also, provide the expected output you would like to see.

You can insert dummy rows or reinsert your existing data to create new custom rows

https://community.spotfire.com/questions/how-add-calculated-column-and-rows-cross-table

[Tymoteusz Grzybowski]

Hi Khushboo R,

thank you for reply. please find excel file for calculations. example based on two days of sales.

thank you for help.

regards,

tymek

[Khushboo Rabadia]

There is no direct functionality to apply filter while fetching records from source before Spotfire 10.1 (In Spotfire 10.1, you have filter rows transformation which can replace the "Calculate New Column" step in below approach). You can have below workaround.

1. Select File > Add Data Tables...

Source: Data loaded from file

Type: Microsoft Excel Workbooks

Location: C:Desktopsum_over_rows.xlsx

Worksheet: 1

Last reload: 5/2/2019 11:43 AM

2. Select Insert > Rows...

Source: Data loaded from file

Type: Microsoft Excel Workbooks

Location: C:Desktopsum_over_rows.xlsx

Worksheet: 1

Added transformations

Transformation name: Calculate new column

Column name: Prod2

Expression: case when [PRODUCT] in ("Sp (R03K2)", "Sr (R03K2)") then "Sp (R03K2) + Sr (R03K2)" end

 

Transformation name: Pivot

Row identifiers:

DAY

YEAR

MONTH

LEVEL

Value columns and aggregation methods:

Max(Prod2)

Sum(SALES UNITS)

Column titles: (None)

Column naming pattern: %V

Transfer columns and aggregation methods: (None)

Transfer column naming pattern: %A(%T)

 

Last reload: 5/2/2019 11:44 AM

Matching behavior: Tries to match the specified columns when data is loaded.

Matched columns:

DAY DAY

YEAR YEAR

MONTH MONTH

LEVEL LEVEL

PRODUCT Prod2

SALES UNITS SALES UNITS

Ignored columns: (None)Basically, we are replacing the product value to null for whichever products you do not need and summing up the sales value for the rest.

Note: You will need to insert rows again from excel or source, as you cannot insert rows > datatable used in current analysis, as it will lead to cyclic dependency.

You can also use TERR to achieve what you want.

https://community.spotfire.com/questions/it-possible-filter-out-some-data-when-adding-new-datatable-existing-datatable