Hi,
I have some sales histories by different customer ID and I want to classify their total spending in different intervals in a straight table. This is the result after I filter a particular customer with 11 repeat purchases but what I want is to group all transactions in one single line with total sales in 46K.
| Spending | Net sales | AUR | Tx Count | IPT |
| 46K | 2,541 | 11 | 1.6 |
| 1-3,000 | 6K | 1,419 | 3 | 1.3 |
| 3,001-6,000 | 22K | 2,229 | 6 | 1.7 |
| 6,001-9,000 | 6K | 3,029 | 1 | 2.0 |
| 9,001-12,000 | 12K | 5,856 | 1 | 2.0 |
This is the calculated dimension I am using and I've tried different way, like using Sum([Invoice Net Amount in HKD]) or Aggr() to group invoice amount by customer ID but no luck.
=Dual(num(SubField(Class([Invoice Net Amount in HKD],vSpendingInterval*1000)+1,' ',1),'#,##0')&'-'&num(SubField(Class([Invoice Net Amount in HKD],vSpendingInterval*1000),' ',5),'#,##0'),
num(SubField(Class([Invoice Net Amount in HKD],vSpendingInterval*1000),' ',1),'#,##0'))
