Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

mohamed_elhamro
New Contributor

Calculated field based on customer history : QlikSense capability data enrichment

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 idcustomeridCategoryVenue datePayment status#days since last VenueLast Payment status
35158497855food29/09/2016Paid17Paid
54659217321food04/01/2017Unpaidunknownunknow
21548956321non food01/02/2017Unpaid28Unpaid
32658754215food02/03/2017Paid1Paid
54659887321non food01/05/2017Paid89Unpaid
15847989145non food27/09/2017Paid2Paid

Thank you for any help.

Kind Regards,

Mohamed.