Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikknow
Contributor II
Contributor II

Create flag to track change and display previous value

Hi all,

I am trying to create a flag to track whenever there is a change in fact value based on ID and Month year.And also show the previous fact value in separate column when ever there is chnage happened if not show its previous  value.

Input:

clipboard_image_0.png

Output:

clipboard_image_1.png

Thanks in advance

Labels (1)
1 Solution

Accepted Solutions
sunny_talwar

May be this

Table:
LOAD [App ID],
	 Date(Month, 'MMM-YY') as Month,
	 Sales;
LOAD * INLINE [
    App ID, Month, Sales
    3233, 08/01/2019, 342
    3233, 07/01/2019, 222
    3233, 06/01/2019, 222
    3233, 05/01/2019, 234
    3233, 04/01/2019, 112
    3233, 03/01/2019, 112
    3233, 02/01/2019, 456
    3233, 01/01/2019, 587
];

FinalTable:
LOAD *,
	 If([App ID] = Previous([App ID]), If(Sales = Previous(Sales), Peek('Previous Sales'), Peek('Sales')), Sales) as [Previous Sales],
	 If([App ID] = Previous([App ID]), If([Sales] = Previous([Sales]), 'N', 'Y'), 'Y') as [Change Flag]
Resident Table
Order By [App ID], Month;

DROP Table Table;

View solution in original post

2 Replies
sunny_talwar

May be this

Table:
LOAD [App ID],
	 Date(Month, 'MMM-YY') as Month,
	 Sales;
LOAD * INLINE [
    App ID, Month, Sales
    3233, 08/01/2019, 342
    3233, 07/01/2019, 222
    3233, 06/01/2019, 222
    3233, 05/01/2019, 234
    3233, 04/01/2019, 112
    3233, 03/01/2019, 112
    3233, 02/01/2019, 456
    3233, 01/01/2019, 587
];

FinalTable:
LOAD *,
	 If([App ID] = Previous([App ID]), If(Sales = Previous(Sales), Peek('Previous Sales'), Peek('Sales')), Sales) as [Previous Sales],
	 If([App ID] = Previous([App ID]), If([Sales] = Previous([Sales]), 'N', 'Y'), 'Y') as [Change Flag]
Resident Table
Order By [App ID], Month;

DROP Table Table;
qlikknow
Contributor II
Contributor II
Author

Thanks Sunny