alteryx Community

# Alteryx Designer Desktop Discussions

SOLVED

## Multirow formula

8 - Asteroid

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?

5 REPLIES 5
7 - Meteor

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

9 - Comet

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

8 - Asteroid

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!

8 - Asteroid

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

14 - Magnetar

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.

Labels