Table object show current and last month data

[Harish_Qlik_user]

Hi,

I want to show comparison of two months based on the selection, ie., previous month data and current month data in table. If user selects (12/1/2019 and 11/1/2019) then I need to show data for 12/1/2019 with 11/1/2019 data in one table  as comparison. 

I need to get the corresponding value  of previous month  based on the KEY column.

Below is the raw data I have.  I need to get Level,Status and Date from previous month data to show with current month data.

TimeStamp	Key	A_ID	P_ID	C_ID	Level	Status	Date
12/1/2019	A67B49C81	A67	B49	C81	4	Open	10/20/2020
12/1/2019	A85B98C15	A85	B98	C15	5	Closed	11/19/2020
12/1/2019	A65B61C53	A65	B61	C53	3	Open	9/27/2020
12/1/2019	A80B29C69	A80	B29	C69	4	Open	8/10/2020
12/1/2019	A37B37C49	A37	B37	C49	2	Open	6/12/2020
12/1/2019	A87B47C59	A87	B47	C59	5	Open	9/10/2020
12/1/2019	A99B72C64	A99	B72	C64	5	Closed	7/24/2020
12/1/2019	A100B12C91	A100	B12	C91	4	Open	5/26/2020
11/1/2019	A67B49C81	A67	B49	C81	3	Open	11/8/2020
11/1/2019	A85B98C15	A85	B98	C15	1	Open	12/5/2020
11/1/2019	A65B61C53	A65	B61	C53	3	Open	10/16/2020
11/1/2019	A80B29C69	A80	B29	C69	5	Closed	8/27/2020
11/1/2019	A37B37C49	A37	B37	C49	1	Open	7/3/2020
11/1/2019	A87B47C59	A87	B47	C59	5	Open	9/27/2020
11/1/2019	A99B72C64	A99	B72	C64	5	Closed	8/6/2020
11/1/2019	A100B12C91	A100	B12	C91	4	Closed	6/5/2020
10/1/2019	A67B49C81	A67	B49	C81	1	Closed	6/27/2020
10/1/2019	A85B98C15	A85	B98	C15	2	Open	4/16/2020
10/1/2019	A65B61C53	A65	B61	C53	4	Open	10/17/2020
10/1/2019	A80B29C69	A80	B29	C69	5	Open	10/28/2020
10/1/2019	A37B37C49	A37	B37	C49	2	Open	9/9/2020
10/1/2019	A87B47C59	A87	B47	C59	2	Closed	8/4/2020
10/1/2019	A99B72C64	A99	B72	C64	2	Open	6/5/2020
10/1/2019	A100B12C91	A100	B12	C91	5	Open	9/7/2020

Below is the output it should look like.

TimeStamp	Key	A_ID	P_ID	C_ID	Level	Status	Date	Prev_Level	Prev_Status	Prev_Date
12/1/2019	A67B49C81	A67	B49	C81	4	Open	10/20/2020	3	Open	11/8/2020
12/1/2019	A85B98C15	A85	B98	C15	5	Closed	11/19/2020	1	Open	12/5/2020
12/1/2019	A65B61C53	A65	B61	C53	3	Open	9/27/2020	3	Open	10/16/2020
12/1/2019	A80B29C69	A80	B29	C69	4	Open	8/10/2020	5	Closed	8/27/2020
12/1/2019	A37B37C49	A37	B37	C49	2	Open	6/12/2020	1	Open	7/3/2020
12/1/2019	A87B47C59	A87	B47	C59	5	Open	9/10/2020	5	Open	9/27/2020
12/1/2019	A99B72C64	A99	B72	C64	5	Closed	7/24/2020	5	Closed	8/6/2020
12/1/2019	A100B12C91	A100	B12	C91	4	Open	5/26/2020	4	Closed	6/5/2020

I tried adding columns in table and in time stamp column used max to show latest but not able to get the previous month data using 'only' function.

It would be great if anyone can guide me the resolve this issue. 

[rubenmarin]

Hi, you can play with set analysis to show some columns using Max(TimeStamp) and others with Max(timeStamp, 2), wich filters the second max selected date.

Sample attached.

[Kushal_Chawda · Accepted solution]

I would suggest to create previous field in script so that it will be easy for you to use max(Timestamp) 

T1:
LOAD
    "TimeStamp",
    Key,
    A_ID,
    P_ID,
    C_ID,
    Level,
    Status,
    date(date#("Date",'MM/DD/YYYY')) as Date
FROM [lib://Qlik web]
(html, utf8, embedded labels, table is @1);

T2:
NoConcatenate
Load *,
     if(Key=Previous(Key),Previous(Level),Null()) as Prev_Level,
     if(Key=Previous(Key),Previous(Status),Null()) as Prev_Status
Resident T1
Order by Key,"TimeStamp";

Drop Table T1;

[Harish_Qlik_user]

@rubenmarin I already tried but when I select any date it shows blank for previous month data.

[rubenmarin]

Hi, by your description: "If user selects (12/1/2019 and 11/1/2019) then I need to show data for 12/1/2019 with 11/1/2019 data in one table  as comparison. " I thought user needs to select two dates, not only one, my solution allows to select 12/1/2019 and 10/1/2019 and compare both.

If you want to only select one date and always compare to the last you can also use "=Only({<TimeStamp={'$(=AddMonths(Max(TimeStamp),-1))'}>} Level)" but in that case it will be easier have it precalculated at script.