Calculated field based on customer history : QlikSense capability data enrichment

[Anonymous]

Hello,

I am a QlikSense new user. I am trying to see how I can create new calculated fields within Qlik Sense to be able afterwards to use predictive models.

Indicative example:

* To produce predictive variables, I need to enrich each row of the db ("shopping actions" table) with a new field. Each row represents a shopping action from a customer abc (customer id, payment status, venue date,...). For row "x", I need to have a field (column to add) describing "past buying behaviour from this customer" corresponding at the "x" date in this row (venue date).

I need to go through the whole "shopping actions" table focusing only on rows where customer id = "abc" AND venue date < venue date of row "x".

Below and example:

* the fields 1 to 5 are given from client

* the fields from 6 and 7 should be calculated and based on cutomers history

** example: customer 321 came 3 times

*****the 1st stime he was new and then no field to calculate as no history

*****the 2nd time

************value "28" is calculated from difference between current venue date and last venue date //

************value "Paid" is calculated from payment status in his last shoppingAction column.

ShoppingAction id	customerid	Category	Venue date	Payment status	#days since last Venue	Last Payment status
35158497	855	food	29/09/2016	Paid	17	Paid
54659217	321	food	04/01/2017	Unpaid	unknown	unknow
21548956	321	non food	01/02/2017	Unpaid	28	Unpaid
32658754	215	food	02/03/2017	Paid	1	Paid
54659887	321	non food	01/05/2017	Paid	89	Unpaid
15847989	145	non food	27/09/2017	Paid	2	Paid

Thank you for any help.

Kind Regards,

Mohamed.