Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'm working on an expression to determine the ratio of customers for a dimension to that of customers who have purchased other products in the product hierarch(ies) that the dimension shares.
"For any given dimension (or combination of dimensions)
The number of unique identified customers divided by
The number of unique identified customers in the Product Hierarchy 3, stores & weeks that the dimensions apply to for the selected timeframe"
My current solution (outlined above) does work correctly, but it is rather slow in larger apps (Fact >300M rows).
I basically created a key that includes the year_week and store with the product hierarchy 3 mapped in via applymap.
I then use the key to create a side table to pull the relevant customer(s) for each of the keys into a lookup table, which is used for the denominator of my expression (count(distinct customer) / count(distinct product_group_customer)).
My question is, is there another expression I could use to perform the same calculation within the Fact (not have to create the lookup table)?
I've tried a few variants using set analysis, P(), total, etc but can't seem to get the correct numbers.
For example, if the product is the dimension, I want the denominator to ignore the dimension but not the product_group_key that the product belongs to (for product A, the customers who bought product A / customers who bought other products in the same product_hierarchy 3 that the product belongs to,, in the same weeks and stores that product A sold).
The pool of customers for the denominator would need to adjust correctly as any dimension (or combination) can be added to the table.
I think I've been dancing around an answer but just can't seem to see it.
I think we are going to need some dummy data to help our minds dance alongside yours, alongside the desired output of course.