Compare the annual calendar with the entry date and set the flag.

[reporting_neu]

Hi there,
I would like to classify new customers.
New customers are all customers for whom the creation date is a maximum of 180 days from the selected date.

The evaluation should start from 01.01.2021.

Example:
The customer was created on 03/15/2021 (createdate).
The date 17.08.2021 was selected in the selection. The customer is displayed as a new customer because 03/15/2021 was no more than 180 days ago. If I display 09/30/2021, the customer is no longer a new customer.

I would like to create a help table for myself. This table should contain every calendar day, every customer number and a flag (customer (0) / new customer (1)).

When creating this for the first time, it must be checked whether the customer is a new customer on each individual calendar day.

In the end, the table should look like this:

Date	Customer	Flag
01/01/2021	100	1
01/01/2021	101	0
01/01/2021	102	0
01/01/2021	103	1
01/02/2021	100	1
01/02/2021	101	1
01/02/2021	102	0
01/02/2021	103	0
01/03/2021	100	1
01/03/2021	101	1
01/03/2021	102	0
01/03/2021	103	0

After this table has been created once, every new day should be added.

Do you have any ideas how I can do that?

[anthonyj · Accepted solution]

Hi @reporting_neu ,

I've added a solution here that loads a distinct list of customers and their start dates. I then loop through all the days from the 1st of January 2021 and do a natural join to the customer table. This results with a table that shows every day against every customer. I add a condition that results in a flag of a new customer or not based on the date.

//The loop will remain dynamic from 1st of Jan to today's date due to the Today() function

data:
Load
date(minDate +IterNo()-1) as Dates
while minDate +IterNo()-1 <= today()
;
load
* Inline [
minDate
01/01/2021
];

//Join a distinct list to the dates table above

join(data)
load * Inline [
Customer, StartDate
100, 10/12/2020
101, 01/03/2021
102, 25/06/2021
103, 04/04/2021

];

//Final output includes a condition flagging the date and customer as new or not on a given date.

output:
load *,
if(StartDate +180 > Dates, Dual('New Customer', 1), Dual('No', 0) ) as [New Customer]
Resident data;

drop table data;

//End Code

Qlik holds values in a table as both string and numeric so the dual function allows you to put a meaningful name against the flag while at the same time being able to use in aggregations like sum( ) and Avg( ).

I hope this helps with your data.

Thanks

Anthony

[reporting_neu]

Perfect 👍 THX!