Calculating one column from other column

[Alteryxexpert]

I have Data Below 

sub item	item	Split	Value	Date
Chair_4	Chair		790,000	12/31/2023
Chair_3	Chair		45,454	12/31/2023
Chair_2	Chair		777555	12/31/2023
Chair_1	Chair		565,666	12/31/2023
Chair_0	Chair		888,999	12/31/2023
Table_4	Table		790,000	12/31/2023
Table_3	Table		45,454	12/31/2023
Table_2	Table		777555	12/31/2023
Table_1	Table		565,666	12/31/2023
Table_0	Table		888,999	12/31/2023
Chair_4	Chair		790,000	1/1/2024
Chair_3	Chair		45,454	1/1/2024
Chair_2	Chair		777555	1/1/2024
Chair_1	Chair		565,666	1/1/2024
Chair_0	Chair		888,999	1/1/2024
Table_4	Table		790,000	1/1/2024
Table_3	Table		45,454	1/1/2024
Table_2	Table		777555	1/1/2024
Table_1	Table		565,666	1/1/2024
Table_0	Table		888,999	1/1/2024

I want to calculate the split(3rd Column) Based on the value column.

The Logic is :

For a specific period(31-12-2023) the calculation for chair_4 should be, 79000/(790,000 +45,454 +777555+565,666 +888,999), and for chair_3 it should be 45454/(790,000 +45,454 +777555+565,666 +888,999) and it should calculate in the same way for all the items.

Datatype:

Value:Numeric

Date:Date

Rest all String

[ShankerV]

Hi @Alteryxexpert 

It can be achieved with the below steps.

Step 1: RecordID tool

Step 2: Sort tool

Step 3: Formula tool

Step 4: Select tool

Step 5: Multi-Row Formula tool

Step 6: Sort tool

Many thanks

Shanker V

[ShankerV]

Hi @Alteryxexpert 

Please find the detailed steps if more assistance is needed to the above response.

Step 1: RecordID tool

Step 2: Sort tool

Name -> Ascending

RecordID -> Descending

Step 3: Formula tool

Output column: 31-Dec-23

ToString(ToNumber([31-Dec-23]))

Step 4: Select tool

Convert Type of column Split and 31-Dec-23 as Double

Step 5: Multi-Row Formula tool

Update Existing Field: Split

IF [item] = [Row-1:item]
THEN [31-Dec-23] + [Row-1:31-Dec-23]
ELSE [31-Dec-23]
ENDIF

Step 6: Sort tool

RecordID -> Ascending

Hope this helps!!!

Many thanks

Shanker V

[Alteryxexpert]

can you give it in the workflow please?

[ShankerV]

Hi @Alteryxexpert 

Apologies, could not upload the workflow due to some error.

Please do replicate the above steps as it worked.

Many thanks

Shanker V

[aatalai]

@Alteryxexpert does the workflow attached help?

[Alteryxexpert]

 column name will change for upcoming months, currently it is 31-Dec-2023, next it will be 01-Jan-2024 ,02-Feb-2024 and so on, and this calculation needs to be dynamic

[Alteryxexpert]

Also Is there a way to achieve the same formula using the below data which is before transform,

sub item	item	Split	Value	Date
Chair_4	Chair		790,000	12/31/2023
Chair_3	Chair		45,454	12/31/2023
Chair_2	Chair		777555	12/31/2023
Chair_1	Chair		565,666	12/31/2023
Chair_0	Chair		888,999	12/31/2023
Chair_4	Chair		790,000	1/1/2024
Chair_3	Chair		45,454	1/1/2024
Chair_2	Chair		777555	1/1/2024
Chair_1	Chair		565,666	1/1/2024
Chair_0	Chair		888,999	1/1/2024

if this is achievable then the workflow will work dynamically

[aatalai]

@Alteryxexpert this might help with the pre change

[Alteryxexpert]

Values are numeric here, in your workflow it is string, I can't use replace on top of a numeric field also can't convert to str