Hi
This is the output file. In this, Billing FTM = Val.in.rep.cur + Adjustment + True Up.
The Adjustment & True Up columns are subjected to user interpretation. If the user changes the Adjustment or True Up, it must be reflected under Billing FTM.
For eg., for the 1st row, if Adjustment is changed to 100 and True Up by another 100, Billing FTM must reflect 5200.34
The problem is that once the Output file is created, Alteryx's job is finished. Even if the user modifies the value in those 2 columns, it will not be reflected under Billing FTM.
My idea is to somehow copy the formula, Val.in.rep.cur + Adjustment + True Up behind every cell. Just like how Excel works.
Is this possible? Or if there is another method, please let me know
Row | G | H | I | M |
1 | Val.in rep.cur. | Adjustment | True Up | Billing FTM |
2 | 5000.34 | 0 | 0 | |
3 | 5000.23 | 0 | 0 | |
4 | 1234.55 | 0 | 0 | |
5 | 1266.22 | 0 | 0 | |
6 | 14047.58 | 0 | 0 | |
7 | 11459.48 | 0 | 0 | |
8 | -6402.01 | 0 | 0 | |
9 | -2411.94 | 0 | 0 |
If you are outputting to excel you can just write the formula into the field. You need to work out what your column letters and row numbers are and add them in.
So in the formula tool you have something like
Billing FTM
Val.in.rep.cur + Adjustment + True Up
but you want something like
Billing FTM
"=SUM(A" + [RowID] + ":C" + [RowID] + ")"
Hi @Carlithian
I have updated the table with rows and columns. Would you please explain based on that?
So here is an example, but there are some important things to consider
RecordID - this is created in the workflow, I am assuming that your file will have data from row 2 down, with row 2 containing the headers.
The columns for the formula are hardcoded in this example, to be G H and I, as such I have added in filler columns so that the data sits in the correct column for the output. If this needs to be dynamic you will need to do work to identify what the excel column letter would be within the workflow itself.
Hope this helps
Thanks @Carlithian
Let me try this.
Brilliant @Carlithian
The solution works perfectly. One question though. For now, I will tell them that the columns need to be static but what if the column is dynamic? How can I handle such a situation?
Okay, imagine that instead of G H and I the columns were now in D H and K. The name of the field should still be the same
Transpose the data in alteryx so you have something and then give it a row ID
Name, Value
field 1, 1
field 2, 2
field 3, 3
Val.in rep.cur., 4
You can convert this value to a letter by converting it to a char
CharFromInt([Value] + 64)
Because the unicode value for A is 64, adding 64 to the value will mean that the first row will give you back A, which is its positional equivalent in the excel file.
Name, Value
field 1, A
field 2, B
field 3, C
Val.in rep.cur., D
I would then filter out the rows that relate to the columns you care about, update the Name to include _columnReference or something after it. Transpose the data back, then use that field in the creation of the excel formula.
"=Sum(" + [Val.in.rep.cur_columnReference] + " + ToString([RecordID]) + "," + [Adjustment_columnReference] +"" + ToString([RecordID]) + ", + [True Up_columnreference] + " + ToString([RecordID]) + ")"
Thanks @Carlithian
I am learning everyday.
Sorry, it has been just 3 months since I have started to work on Alteryx.
I need to ask you for one more calculation.
15% of the Billing FTM must be calculated and populated in another column called Markup @15%.
I cannot directly multiply Billing FTM * 15 / 100 because Billing FTM is a string.
Please let me know how can I solve this.
Thanks
Bobji
To get around this you will need the Markup@15% column to also be a formula in excel, as it needs to update as the Billing FTM column updates
I think it will be something like this, but look at how you would create the work in excel and replicate the forumla with that dynamic element from RecordID
"=M" + [RecordID] + "/100"