Calculate difference | First row as constant

[maier_alexander_MO]

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. 

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

Kind regards,

Alex

[RolandSchubert]

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

[Jonathan-Sherman · Accepted solution]

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.

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

[TomWelgemoed]

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?

[LukeG · Accepted solution]

Hi @maier_alexander_MO ,

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

[maier_alexander_MO]

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

[maier_alexander_MO]

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