Active customers kpi, how to define the fastest expression

[#1]

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	PRODUCT	ACTIVES
Family 1	Product 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

[#2]

Some help please.

Any ideas?