Previous Function

[Anonymous]

Hi ,

I want to find previous of monthly value ,but data received on daily basis

     Input

ID	MonthYear	Status	Date
1234	Jul-17	Done	7/31/2017
1234	Aug-17	InProgress	8/31/2017
1234	Sep-17	InProgress	9/18/2017
1234	Sep-17	InProgress	9/17/2017
1234	Sep-17	InProgress	9/16/2017
1234	Sep-17	InProgress	9/14/2017
1234	Sep-17	InProgress	9/13/2017
1234	Sep-17	InProgress	09/12/2017
1234	Sep-17	InProgress	09/10/2017
1234	Sep-17	InProgress	09/09/2017
1234	Sep-17	InProgress	09/07/2017
1234	Sep-17	InProgress	09/06/2017
1234	Sep-17	InProgress	09/05/2017
1234	Sep-17	InProgress	09/04/2017
1234	Sep-17	InProgress	09/03/2017
1234	Sep-17	InProgress	09/02/2017

Output.Required

ID	MonthYear	StatusCheck
1234	Jul-17	NoChnage
1234	Aug-17	Change
1234	Sep-17	NoChnage

[techvarun]

What is the definition of Nochange and Change here?

[Anonymous]

there is change in status in Aug month from Done to inProgress

[Anil_Babu_Samineni]

May be this?

LOAD Num(ID) as ID,

    MonthName(MonthYear) as MonthYear,

    Status,

    Date(Date) as Date,

    If(Status = Previous(Status), 'NoChange', 'Change') as StatusCheck

FROM

(ooxml, embedded labels, header is 1 lines, table is Sheet1, filters(

Remove(Row, Pos(Top, 22)),

Remove(Row, Pos(Top, 21)),

Remove(Row, Pos(Top, 20)),

Remove(Row, Pos(Top, 19)),

Remove(Row, Pos(Top, 18))

));

[antoniotiman · Accepted solution]

Hi,

Temp:
LOAD ID,
MonthName(MonthYear) as MonthYear,Only(Status) as Status,
Max(Date) as Date
FROM
https://community.qlik.com/servlet/JiveServlet/download/1348183-296465/Sample.xlsx
(ooxml, embedded labels, header is 1 lines, table is Sheet1)
Group By ID,MonthYear;
LOAD ID,MonthYear,
If(Status=Previous(Status) or RowNo()=1,'NoChange','Change') as StatusCheck
Resident Temp
Order By ID,MonthYear;
Drop Table Temp;

Regards,

Antonio

[Anil_Babu_Samineni]

If you need first row as "No Change" you could try this?

     If(Status = Previous(Status) or RowNo() = 1, 'NoChange', 'Change') as StatusCheck

[Anonymous]

why u have used only function

[antoniotiman]

Because Group By clause needs aggregation function like Only(),Min(),Max(),Avg() atc.

[Anonymous]

Thanks