Multirow formula

[rajputakansha]

Hi,

I am trying to use Multi row formula to compare the Old values from previous period and New values from current period for each  unique ID.

My output should be "Desired Value"

Period	ID	New Value	Old Value	Desired Value
P1	A	1000	1700	0
P2	A	1700	1900	0
P3	A	1900	2500	0
P4	A	2700	3100	-200
P1	B	1400	1000	0
P2	B	1000	1800	0
P3	B	2000	2400	-200
P4	B	2400	5800	0
P1	C	3000	3200	0
P3	D	6400	6800	0

If I use [Row-1:Old Value]-[New Value], I am getting:

Period	ID	New Value	Old Value	Difference in Old value and new value each period
P1	A	1000	1700	-1000
P2	A	1700	1900	0
P3	A	1900	2500	0
P4	A	2700	3100	-200
P1	B	1400	1000	-1400
P2	B	1000	1800	0
P3	B	2000	2400	-200
P4	B	2400	5800	0
P1	C	3000	3200	-3000
P3	D	6400	6800	-6400

Can anyone please suggest a correct logic for this?

Thanks for your help!

[AhmedAlZabidi]

Hello,

You can use the following formula:

if IsNull([Row-1:Old Value]) or [Row-1:Old Value] == 0 then 0 else [Row-1:Old Value]-[New Value] endif

[Martyn]

In the Multirow Formula tool you need to take into account what you have specified for "Values for Rows that don't exist".

The issue is that the first row in each group is using a zero value for the [Row-1:Old Value].

Put some extra logic in to take this into account.

For instance

IF [Row-1:Old Value] = 0

THEN 0

ELSE [Row-1:Old Value]-[New Value]

ENDIF

[rajputakansha]

Thanks @AhmedAlZabidi , @Martyn ,

The logic does work well when I already have some 0s the New and Old value columns.

For example:

Period	ID	New Value	Old Value	Difference in Old value and new value each period
P1	A	0	0	0
P2	A	0	0	0
P3	A	1900	2500	0  [This should be -1900]
P4	A	2700	3100	-200

Can you please suggest how to work around in this case?

Thank you!

[rajputakansha]

Correction: The logic doesn't work well when I already have some 0s the New and Old value columns.

[T_Willins · Accepted solution]

Hi @rajputakansha,

You can solve this by changing the Multi-Row Formula Values for Rows that don't Exist to Null while keeping the field type to Int32.