1 Reply Latest reply: Jan 26, 2012 9:55 AM by marquitus RSS

    Active customers kpi, how to define the fastest expression

      Hello and thanks in advance,

       

       

      I have a problem developing the ACTIVE USER KPI:  Customers that have bought inamount > 0 €

       

      I need to calculate how many customers are active on each Product, Family,...  but customer dimension is not showed on Qlikview objects (crosstabs).

       

      *1 Note: customer can bought 10 € of a product in week 1, then return 5 € (data is stored as -5€)in week 2, then return 5 € (data is stored as -5€) week 3; it means on week 1 and 2 is ACTIVE, but in accumulate to week 3 is NOT ACTIVE.

       

      *2 Note: customer can bought 10€ of product 1, and return 20 € of product 2; it means:

       

      FAMILYPRODUCTACTIVES
      Family 1Product 1   1        (at least customer X bought 10€)

      Product 2   0         (the customer X bought -20€)









      TOTAL FAMILY 1
         0           (the customer X bough 10€ - 20€ = -10€)


       

       

       

      1. The working strategy is to differenciate PRODUCT DETAIL and TOTAL FAMILY with DIMENSIONALITY() function, and then calculate locally the amount for each customer

       

      // Calculate aggregation at product x customer detail

      IF (Dimensionality() = 1,         count( aggr(  IF ( sum(sales) > 0, 1)    , FAMILY, PRODUCT, CUSTOMER) )

       

      // Calculate aggregation at family x customer detail

                                                         , count(aggr(  IF ( sum(sales) > 0, 1)    , FAMILY,  CUSTOMER) )

      )

       

       

      This solution works OK, but is very very slow.

       

       

       

      2. I’m trying to use advanced "SET ANALYSIS --> Set Modifiers with advanced searches":

       

      count( {$<CUSTOMER = {“=Sum(sales) > 0”}>}CUSTOMER )

       

      This solution is 10xor 100x times faster, but the calculous is incorrect. If you check the functionality, inner and outer ratio must be the same, then it works, but I don't want this.

       

                      count( {$<CUSTOMER = {“=Sum(sales)> 0”}>} sales )                                                       WORKS OK, but I don't need sales

                      count({$<CUSTOMER = {“=Sum(sales*customercounter) > 0”}>} sales*customercounter)     WORKS OK, but I don't need this

       

      with inner ratio different form outer ratio is NOT WORKING          

       

       

       

       

      3. I think the option of precalculate on script is not possible here (because of notes *1 and *2), and also because really I have 4 dimensions (Product business, Product Line, Product Family and Product),and also I have other objects for Region hierarchy, Retailer hierarchy, and so on…

       

      But maybe I'm wrong.

       

       

       

      I’m open to a different approach, don’t doubt of telling me your ideas.

       

      Regards