community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Community v19.9

The latest release includes several enhancements designed to improve your Community experience!

Learn More
SOLVED

Calculate difference | First row as constant

Highlighted

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:

 

image.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

After the Union, the table format is as follows:

 

 image.png

 

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. 

 

Thank you very much in advance for your help on this! 

Kind regards,

 

Alex

 

 

 

 

 

Alteryx Certified Partner
Alteryx Certified Partner

Hi @maier_alexander_MO ,

 

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").

 

Hope this is helpful.

 

Best regards

 

Roland

Alteryx Partner

Hi @maier_alexander_MO,

 

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.

image.png

 

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

 

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
Alteryx

Hi @maier_alexander_MO ,

 

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

 

Multirow.PNG

 

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

Hi @Jonathan-Sherman ,

 

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

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