I can't work out how to dynamically create a pseudo-dimension that shows the percentiles of orders based on their spend.
I've managed to create a static view that ignores all filters but can't find a way to replicate this dynamically. It's a real bodge but it does produce the table and graph my boss wants.
My static solution is to create a table during load, linked to the main table by order number. The table is ordered by order spend, has one row per order and by dividing the RowNo() of each order by the total number of orders divided by a hundred and using Ceil() I get a percentile dimension. Each percentile holds the same number of orders so I can use that as an x-axis of a graph. If you use any filters though the graph shows incorrect information.
The resulting table looks a bit like this (only with millions of records this would work for 500)...
|
OrderNum
|
RowNum
|
Percentile
|
|
Ord1
|
1
|
1
|
|
Ord2
|
2
|
1
|
|
Ord3
|
3
|
1
|
|
Ord4
|
4
|
1
|
|
Ord5
|
5
|
1
|
|
Ord6
|
6
|
2
|
|
Ord7
|
7
|
2
|
|
Ord8
|
8
|
2
|
|
Ord9
|
9
|
2
|
|
Ord10
|
10
|
2
|
|
Ord11
|
11
|
3
|
I can't work out how to recreate this dimension dynamically so when a filter is applied the number of orders per percentile changes and the ordering of them is kept.
I'm hoping I've missed something really obvious.
