cancel
Showing results for
Did you mean:

# Alteryx designer Discussions

SOLVED

## Calculate difference | First row as constant

Meteor

Hi all,

I have detailed information on the financial status on a daily basis. For reporting reasons (done within Tableau) I also want to show the total financial status for the current and previous week, last month and last quarter

For this i created the down below workflows that give me exactly the required rows:

After the Union, the table format is as follows:

What I am trying to get (probably via Multi-Row Formula?) is a new column that for Record 1 shows me the actual amount (Closing Balance EUR). Each record below that should however be the difference compared to the Closing Balance from row 1.

So row 1 should be a constant within the formula and for all rows below that, the difference should be calculated compared to row 1.

Kind regards,

Alex

Alteryx Certified Partner

I think, the Multi-Row Formula approach is the best solution.

The formula could look like this:

IF IsNull([Row-1:Closing Balance EUR]) THEN
[Closing Balance EUR]
ELSE
[Row-1:Closing Balance EUR] - [Closing Balance EUR]
ENDIF

The setting "Values for Rows that don't Exist" should be "NULL", so for row 1 (no "Row-1" exists) the IF condition is true and the new field receives the "Closing Balance" value of the current row. For all other rows the difference is calculated. This should work for multiple accounts also (using "Group By").

Best regards

Roland

Alteryx Partner

I think this is what you're looking to achieve? I may have your difference to formula the wrong way round, couldn't quite tell which way you wanted it.

Regards,

Jonathan

Alteryx Partner

Hi,

I think you're on the right track - you simply need to get Record 1's value calculated and then in the multi-formula tool you will do something like this:

if Record = Record1

Closing Balance EUR

else  Closing Balance EUR - [Record-1: Closing Balance EUR]

endif

Hope that helps - if not, post us some sample data?

Alteryx

Here is a solution I built out with a sample Closing Balance EUR column:

I used two multi-row formulas. One of them establishes the value for Closing Balance Row 1. This value is then used in the second multi-row formula to calculate the difference (except for row 1).

Let me know if you have any questions behind the logic I used here, or if the solution needs to be changed at all. Hope this is helpful!

Luke

Meteor

thank you so much, this exactly what I was trying to do and solved my problem!

@TomWelgemoed  & @RolandSchubert Thank you so much for your help on this, the formulas will probably help me on other calculations I have to do as well! For this special case I do however require the difference for each row compared to the first row. I think I should have written this a bit clearer in the problem description.

Have a nice day and thank you!

Kind regards,

Alex

Meteor

Hi @LukeG ,

that is a great solution as well and it works perfectly. Now I am spoilt for the choice!

Thank you very much for the help and kind regards,

Alex

Labels