Skip to main content
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
Showing results for 
Search instead for 
Did you mean: 
Partner - Contributor III
Partner - Contributor III

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

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:


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.

Labels (1)
3 Replies
Contributor III
Contributor III


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,


Contributor III
Contributor III

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

Specialist II
Specialist II

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

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

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

Drop table Temp;

Exit Script;