Age calculation

[rajinikanth]

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

[chrismarlow]

Hi,

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

Cheers,

Chris.

[stevejoyce · Accepted solution]

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]

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.

[SunilChauhan]

use columns as is  

Date

Flag

ID

add one more Calculation here 

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

[rajinikanth]

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]