Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Coming Aug. 9: New Simplified Authoring for Qlik Sense SaaS – For Details, CLICK HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
50ShadesOfSalty
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:

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
gagewhite
Contributor III
Contributor III

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
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

sidhiq91
Specialist
Specialist

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;