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" PeriodIDNew ValueOld ValueDesired ValueP1A100017000P2A170019000P3A190025000P4A27003100-200 P1B140010000P2B100018000P3B20002400-200P4B240058000 P1C300032000 P3D640068000 If I use [Row-1:Old Value]-[New Value], I am getting: PeriodIDNew ValueOld ValueDifference in Old value and new value each periodP1A10001700-1000P2A170019000P3A190025000P4A27003100-200 P1B14001000-1400P2B100018000P3B20002400-200P4B240058000 P1C30003200-3000 P3D64006800-6400 Can anyone please suggest a correct logic for this? Thanks for your help!

Multirow formula

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!

Datasets

Output

Accepted answers

T_Willins

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.

Multi Row.JPG

Multi Row.yxmd

All comments

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!

T_Willins

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.

Multi Row.JPG

Multi Row.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703