Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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€) |
// 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.
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
But maybe I'm wrong.
I’m open to a different approach, don’t doubt of telling me your ideas.
Regards
Some help please.
Any ideas?