Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
rajinikanth
Contributor III
Contributor III

Age calculation

Hi All,

I have data as below.

Date Flag ID
1-Apr-21 Yes R7
1-May-21 Yes R7
1-Jun-21 Yes R7
1-Jul-21 Yes R7
1-Aug-21 Yes R7
1-Sep-21 No R7
1-Oct-21 No R7
1-Nov-21 Yes R7
1-Dec-21 Yes R7
1-Jan-22 No R7
1-Feb-22 Yes R7
1-Mar-22 Yes R7
1-Apr-22 Yes R7
1-May-22 Yes R7

 

My requirement is to get output as below table.

Date Flag ID Age date
1-Apr-21 Yes R7 1-Apr-21
1-May-21 Yes R7 1-Apr-21
1-Jun-21 Yes R7 1-Apr-21
1-Jul-21 Yes R7 1-Apr-21
1-Aug-21 Yes R7 1-Apr-21
1-Sep-21 No R7 null
1-Oct-21 No R7 null
1-Nov-21 Yes R7 1-Nov-21
1-Dec-21 Yes R7 1-Nov-21
1-Jan-22 No R7 null
1-Feb-22 Yes R7 1-Feb-22
1-Mar-22 Yes R7 1-Feb-22
1-Apr-22 Yes R7 1-Feb-22
1-May-22 Yes R7 1-Feb-22

 

My requirement is the minimum date for an ID when the Flag ='Yes', should be my "Age date", when I have a breakage in Flag for value 'yes'  and it becomes 'No' then my "Age date" should be null, and again when the flag = 'Yes', form there the "Age date" should start again till 'No' appears

Labels (4)
1 Solution

Accepted Solutions
stevejoyce
Specialist II
Specialist II

See below.

 

//loading data from community

source_data:

load
*
from https://community.qlik.com/t5/App-Development/Age-calculation/td-p/1909331
(html, utf8, embedded labels, table is @1)
;


//sort in first part of preceeding load.
//then run row level check to calculate age date
data:
noconcatenate load
*
,if([Flag] = 'No', null()
,if(([Flag] = 'Yes' and peek([Flag]) <> 'Yes'), [Date]
,peek([Age date])
))

as [Age date]
;
load
Date
,Flag
,ID
resident source_data
order by ID, Date asc;
;

drop table source_data;


exit script;

View solution in original post

5 Replies
chrismarlow
Specialist II
Specialist II

Hi,

If you want to do in a chart something like this (should be the same in Qlik Sense as QlikView);

20220325_1.png

Cheers,

Chris.

stevejoyce
Specialist II
Specialist II

See below.

 

//loading data from community

source_data:

load
*
from https://community.qlik.com/t5/App-Development/Age-calculation/td-p/1909331
(html, utf8, embedded labels, table is @1)
;


//sort in first part of preceeding load.
//then run row level check to calculate age date
data:
noconcatenate load
*
,if([Flag] = 'No', null()
,if(([Flag] = 'Yes' and peek([Flag]) <> 'Yes'), [Date]
,peek([Age date])
))

as [Age date]
;
load
Date
,Flag
,ID
resident source_data
order by ID, Date asc;
;

drop table source_data;


exit script;

chrismarlow
Specialist II
Specialist II

Not sure what I was looking at when I did this. Think you have to do the script as you can't get the right number to feed the above function.

IamBack
Champion
Champion

use columns as is  

Date Flag ID

 

add one more Calculation here 

If( Flag='No','null', Date(Min(Date),'DD-MMM-YY'))

 

rajinikanth
Contributor III
Contributor III
Author

Thanks for the support, it worked out for me by adding one more small condition to it.

If([Date]=[Age date],[Age date],

if([Flag] = 'No', null()
,if(([Flag] = 'Yes' and peek([Flag]) <> 'Yes'), [Date]
,peek([Age date])
))) as as [Age date]