Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld starts MONDAY! last chance to register is now ! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Harish_Qlik_user
Contributor II
Contributor II

Table object show current and last month data

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.

TimeStampKeyA_IDP_IDC_IDLevelStatusDate
12/1/2019A67B49C81A67B49C814Open10/20/2020
12/1/2019A85B98C15A85B98C155Closed11/19/2020
12/1/2019A65B61C53A65B61C533Open9/27/2020
12/1/2019A80B29C69A80B29C694Open8/10/2020
12/1/2019A37B37C49A37B37C492Open6/12/2020
12/1/2019A87B47C59A87B47C595Open9/10/2020
12/1/2019A99B72C64A99B72C645Closed7/24/2020
12/1/2019A100B12C91A100B12C914Open5/26/2020
11/1/2019A67B49C81A67B49C813Open11/8/2020
11/1/2019A85B98C15A85B98C151Open12/5/2020
11/1/2019A65B61C53A65B61C533Open10/16/2020
11/1/2019A80B29C69A80B29C695Closed8/27/2020
11/1/2019A37B37C49A37B37C491Open7/3/2020
11/1/2019A87B47C59A87B47C595Open9/27/2020
11/1/2019A99B72C64A99B72C645Closed8/6/2020
11/1/2019A100B12C91A100B12C914Closed6/5/2020
10/1/2019A67B49C81A67B49C811Closed6/27/2020
10/1/2019A85B98C15A85B98C152Open4/16/2020
10/1/2019A65B61C53A65B61C534Open10/17/2020
10/1/2019A80B29C69A80B29C695Open10/28/2020
10/1/2019A37B37C49A37B37C492Open9/9/2020
10/1/2019A87B47C59A87B47C592Closed8/4/2020
10/1/2019A99B72C64A99B72C642Open6/5/2020
10/1/2019A100B12C91A100B12C915Open9/7/2020

Below is the output it should look like.

TimeStampKeyA_IDP_IDC_IDLevelStatusDatePrev_LevelPrev_StatusPrev_Date
12/1/2019A67B49C81A67B49C814Open10/20/20203Open11/8/2020
12/1/2019A85B98C15A85B98C155Closed11/19/20201Open12/5/2020
12/1/2019A65B61C53A65B61C533Open9/27/20203Open10/16/2020
12/1/2019A80B29C69A80B29C694Open8/10/20205Closed8/27/2020
12/1/2019A37B37C49A37B37C492Open6/12/20201Open7/3/2020
12/1/2019A87B47C59A87B47C595Open9/10/20205Open9/27/2020
12/1/2019A99B72C64A99B72C645Closed7/24/20205Closed8/6/2020
12/1/2019A100B12C91A100B12C914Open5/26/20204Closed6/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. 

Labels (2)
1 Solution

Accepted Solutions
Kush
MVP
MVP

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;

View solution in original post

4 Replies
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.

Kush
MVP
MVP

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;

View solution in original post

Harish_Qlik_user
Contributor II
Contributor II
Author

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