Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hi, I don't fully understnd your requirement. Can you ost some sample data and the expected result when there is a selection done?
Maybe this is what you are looking for
https://community.qlik.com/t5/QlikView-App-Dev/Quartile/td-p/844104
Managed to work out what I needed to do by using the following as the dimension...
Ceil(Aggr(RowNo(TOTAL),ORDER_NUM)/($(vNum_CountRows)/100))
It works provided vNum_CountRows is a count of Order Num. I didn't think about aggreagating RowNo().