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:


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

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

         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.