Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted

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
Highlighted

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

Highlighted
Contributor II
Contributor II

Thanks Sunny