Above() Below() against a dimension

jasonrimkus — Tue, 04 May 2021 18:14:11 GMT

I'm trying to compare where a particular store's Count(CustomerID) fits within the average of a subset of stores.

In the table, i'd like to show how the Count(CustomerID) compares to the average +/- 5 of the store's ranked sales. So for example, if i'm looking at store 6, i'd want to calculate the % difference between Store 6 and the average for Stores 1 through 11. If store 7, stores 2-12; store 8, stores 3-13 and so on. What would be the expression to calculate the average across those stores based on the CurrYr_RankSales?

I'm assuming its Above() and Below(), but i'm at a loss on how to calculate it across the CurrYr_RankSales dimension.

Store	CurrYr_RankSales	Count(CustomerID)
Store1	1	158
Store2	2	91
Store3	3	50
Store4	4	282
Store5	5	475
Store6	6	202
Store7	7	291
Store8	8	8
Store9	9	387
Store10	10	76
Store11	11	81
Store12	12	43
Store13	13	129
Store14	14	194
Store15	15	210
Store16	16	364
Store17	17	77
Store18	18	188
Store19	19	227
Store20	20	446
Store21	21	178
Store22	22	221
Store23	23	138
Store24	24	105

Re: Above() Below() against a dimension

JuanGerardo — Tue, 04 May 2021 18:34:49 GMT

Hi @jasonrimkus, I suppose you need a combination of Above and RangeAvg:

RangeAvg(Above(Count(CustomerID), 5, 12))

topic Re: Above() Below() against a dimension in App Development

Above() Below() against a dimension

Re: Above() Below() against a dimension