Active customers kpi, how to define the fastest expression

Sun, 22 Jan 2012 13:54:51 GMT

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

Active customers kpi, how to define the fastest expression

Thu, 26 Jan 2012 14:55:54 GMT

Some help please.

Any ideas?

topic Active customers kpi, how to define the fastest expression in QlikView

Active customers kpi, how to define the fastest expression

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

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

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…

Active customers kpi, how to define the fastest expression

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…