0 Replies Latest reply: Aug 10, 2018 5:21 AM by Mohamed El Hamrouni RSS

    Calculated field based on customer history : QlikSense capability data enrichment

    Mohamed El Hamrouni

      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.