Calculate overall customer frequency and then attributing to dimension

[flygandejakob]

HI!

I'm trying to create a bar chart in QlikSense with stores as the dimension, and then the average customer frequency for each customer who visited that store. The caviat is that I'd like the frequency for each customer to be their total frequency, not just the frequency in that specific store.

You can imagine the data looks like:

Date	Store	Customer	Sales
2020-01-01	A	1	5
2020-02-01	B	1	4
2020-03-01	A	1	7
2020-01-01	B	2	3
2020-02-01	B	2	4

And my desired output (displayed below in a table but will be shown in a bar chart with Store as dimension and average frequency as measure).

Store	Avg. Frequency
A	3
B	2,5

So Avg. Frequency in store A would look at all customers with at least 1 purchase in store A, and then their total frequency across all stores. In the above example it's only Customer 1 who bought in store A and that customer bought 3 times in total (twice in store A and once in store B), so the average should be 3. Both customers shopped in store B, and their total frequencies are 3 and 2 so the average should be 2,5.

What I've tried so far with no success

Option 1:

AVG( Aggr(COUNT( {$<[ORDERDATE.autoCalendar.Year]={2020}>} DISTINCT ORDERID),CUSTOMER))

This one seems to only pick 1 value per customer, and not their total. I checked an individual customer and if they bought 3 times in store A and 5 times in store B, it will only use the 5 time for store B frequency.

Option 2:

AVG( Aggr(COUNT( {$<[ORDERDATE.autoCalendar.Year]={2020}>} DISTINCT ORDERID),CUSTOMER,STORE)

Adding STORE in the aggr function doesn't work either since it's then only counting the frequency in each store.

I've also tried adding TOTAL in the set expression but then it loses the store-connection, and each store will just display the same Avg. Frequency.

I read about the P() function but that doesn't seem to be available in the QlikSense web version I'm using.

I think the solution is that I first somehow need to create a table such as 

Store	Customer	Total_Frequency
A	1	3
B	1	3
B	2	2

and then use these STORE and TOTAL_Frequency values in the graph. But I can't figure out how to create that value on the fly so it's dynamic to any date adjustments or looking at each month separately for example. This would work well as a one-off and looking back from today, but I also want to be able to see the Frequency change over time and then this approach doesn't seem correct.

Any ideas? Let me know if I need to clarify something.

Thanks for reading!

/Jakob

[Brett_Bleess]

Have a look at the following Design Blog post, may be of some help to you:

https://community.qlik.com/t5/Qlik-Design-Blog/Set-Analysis-in-the-Aggr-function/ba-p/1463822

If you want to search for other posts in that area, use this link:

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett