Tracking Inflows Over Time

[LoKi_asterix]

Hi everyone,

I’m sure this is something fairly straightforward, but it’s had me going in circles all day!

I have a dataset covering 2014 to 2025, with 2014 as the base year.
My goal is to flag or extract all investment inflows over this period specifically:

The first time a customer brought in funds (initial investment)

Any subsequent inflows (top-ups) in the following years

While avoiding double-counting or re-flagging previous amounts

I initially used Exists() to filter out customers already present in the base year to identify new ones per year. However, I’m struggling to capture top-ups from existing customers who bring in new funds in later years.

Has anyone solved a similar use case, or can recommend a reliable way to model this in Qlik?

preferably in the script.

Thanks so much in advance! 🙏 @marcus_sommer @marksouzacosta @Lech_Miszkiewicz @igoralcantara @ @rwunderlich @Or @jagan 

[marcus_sommer]

I would probably use interrecord-functions like previous() and peek() within (n) sorted (backwards and forwards) resident-loads to flag the first/last occurrence + creating a continuous counter-field + calculating the date-offset between the records and similar measurements.

It mustn't be only done globally else maybe against years and/or the date-offsets or any accumulation triggers an adjustment to the tracking ...

[rafaelencinas · Accepted solution]

Hi @LoKi_asterix ,

In my opinion, you can try:

- Create a Flag ( First or Not First ) 

MyData:
LOAD * INLINE [
Date | Customer | Value
01/01/2023 |A | 100
01/01/2024 |A | 50
01/01/2025 |A | 50
01/01/2022 |B | 200
01/01/2023 |B | 50

](delimiter is '|');

MapFirst:
Mapping
Load
num(MinDate) & '|' & Customer as _myField
,1
;
Load Min(Date) as MinDate,
Customer
Resident MyData
Group by Customer
;

MyDataNew:
Load *
,if( ApplyMap('MapFirst' , num(Date) & '|' & Customer ,0) =1,'First','Other') as Flag
Resident MyData;
Drop table MyData;

Bye.