topic Re: Calculating value for rows above in Alteryx Designer Desktop Discussions

Calculating value for rows above

Wojtek_Dz — Fri, 15 Sep 2023 10:26:06 GMT

Hi,

could anybody help me with case as below:

A	B	C	D
10	20	20	=B1*C1
12	22	=C1-D1	=B2*C2
14	24	=C2-D2	=B3*C4
16	26	=C3-D3
18	28
20	30
22	32
24	34
26	36

I wanted to calculate the value for column C and D. The value in C1 is static rest of them need to be calculated.

Any ideas ?

Thanks !

Re: Calculating value for rows above

FinnCharlton — Fri, 15 Sep 2023 09:58:25 GMT

Hi @Wojtek_Dz , are the formulas correct? The formula for C2,C3,C4 all reference themselves in the calculation.

Re: Calculating value for rows above

gawa — Fri, 15 Sep 2023 10:02:35 GMT

hi @Wojtek_Dz

Multi row formula is the best. Multi row formula can refer to another records for calculation, which Excel users are already familiar with. In your case, you want to have a static value at first row(C2), then you have to put a conditional statement like below(Do not forget "Values for Rows that don't exist" to NULL, otherwise it will not work properly in this case)

By the way, though I configured your calculation method, and results are going to diverge, which exceeds the limit of INT64 at last row. Could you check your calculation method is surely correct??

Re: Calculating value for rows above

Wojtek_Dz — Fri, 15 Sep 2023 10:27:52 GMT

@FinnCharlton corrected :)

Re: Calculating value for rows above

Wojtek_Dz — Fri, 15 Sep 2023 10:33:03 GMT

thanks @gawa

Yes, I also tried to solve it by multi-row tool but I failed.

In your solution in my opinion there is still missing something.

If we take the row 2 you can noticed that we need for in the calculation from row 1 for C and D.

So iI think that we can't calculate the col C as 1st step, and then calculate col D :(

Re: Calculating value for rows above

FinnCharlton — Fri, 15 Sep 2023 10:40:02 GMT

Hi @Wojtek_Dz , here you go. As the columns are dependent on each other being filled at the same time, we need to transpose the data before we calculate the multi-row formula. These numbers get massive, would be interested to know what you're trying to compute here.

Re: Calculating value for rows above

Wojtek_Dz — Fri, 15 Sep 2023 10:57:11 GMT

thanks @FinnCharlton

but as I replied to @gawa solution if we want to calculate C2 we have to subtract C1 from D2 (which has to be calculated earlier) :(

Re: Calculating value for rows above

FinnCharlton — Fri, 15 Sep 2023 11:02:30 GMT

@Wojtek_Dz , my solution does exactly what the formulas in your table say. To calculate C2, we do C1-D1, D2 is not involved:

If this isn't correct please clarify your formulas.

Re: Calculating value for rows above

Wojtek_Dz — Fri, 15 Sep 2023 13:29:28 GMT

Thanks @FinnCharlton ,

You are right, your solution is cool.

I was wrong :)

Thank you very much for your help !