Create a flag to compare if values have been changing over time

[50ShadesOfSalty]

Hi everyone,

I would like to create a flag that allows me to compare if the values of a certain field have been changing, per customer, over time.

• For this, a new field - [ValueHasChangedComparingPM] - would be created in the Table CustomerMetrics.

Imagine the following scenario in Table CustomerMetrics:

CustomerMetrics:

Customer	MonthYear	Value	ValueHasChangedComparingPM
A	012022	X	No
A	022022	X	No
A	032022	X	No
B	012022	Y	No
B	022022	Y	No
B	032022	Z	Yes
C	012022	U	No
C	022022	O	Yes
C	032022	M	Yes

The field [ValueHasChangedComparingPM] would have the values:

• Yes - if the [Value] of the "previous month" is different than "previous month +1"
• No - if the [Value] of the "previous month" is the same as "previous month+1"
  • If there's no value before to do the comparison, set the default value as No

Has anyone faced a similar issue before that could help me this exercise? I've already tried a lot of ways to do this, but i keep being blocked.

Thank you in advance for any guidance!

Best regards.

[gagewhite]

Hello,

You could use an ORDER BY statement to order the data by Customer and Date. Next, create a field in the table to first check if the Customer is the same, then check if the value has changed. It would look something like IF(Customer <> PREVIOUS(Customer),  'N', IF(Value <> PREVIOUS(Value), 'Y', 'N')) as ValueHasChangedComparingPM. If the Customer has changed, then 'N' because that indicates the next Customer's cluster of Values (thanks to your ORDER BY statement), else if the value has changed then 'Y' because that indicates there's a changed in Value from one time period to the next (also thanks to your ORDER BY statement), else 'N' because that means the Values were the same from one time period to the next.

Hope this helps,

Gage

[gagewhite]

Something I forgot to mention... The ORDER BY statement only works on a RESIDENT load. So you would have to load it in from source first. Also, bear in mind this will remain static if you are applying other filters. To have it be dynamic based off table sorting or filters, I would use a similar formula as above, but in a table, variable, etc., and using ABOVE instead of PREVIOUS

[sidhiq91]

Please use below and kindly mark it as solved if it resolves your issue.

NoConcatenate
Temp:
Load * Inline [
Customer, MonthYear, Value
A, 012022, X
A, 032022, X
B, 012022, Y
B, 022022, Y
B, 032022, Z
C, 022022, O
C, 032022, M
A, 022022, X
C, 012022, U
];

NoConcatenate
Temp1:
Load *,
if(Customer=Peek(Customer) and Value<>Peek(Value),'Yes','No') as [ValueHasChangedComparingPM]
Resident Temp
Order by Customer;

Drop table Temp;

Exit Script;