Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a sales table like this and I need to create a dimension base on the top spenders
Date Customer ID Amount
Jan 2014 C0001 100
Jan 2014 C0002 1000
Jan 2014 C0003 500
Feb 2014 C0004 200
Feb 2014 C0001 700
Feb 2014 C0005 300
.......
.......
Dec 2014 C0100 100
By calculation, the aggregated purchase sum by Customer is like this and we assume there are 100 unique customer sorted by descending purchase for simplicity.
C0002 1000
C0001 800
C0003 500
C0004 200
....
C0100 100
My expected result is the lines in Red, base on the no. of unique customers.
Top % Amount
High Value (Top 30) Sum of top 30 lines
Mid Value (Next 30) Sum of next 30 lines
Low Value (Next 40) Sum of next 40 lines
You can use the rank function to calculate this. See attached example.
Note: it's an example and does not contain your data. So you will have to adapt the solution to your needs. So please don't complain you're not interesting in top Transaction Dates.
You can use the rank function to calculate this. See attached example.
Note: it's an example and does not contain your data. So you will have to adapt the solution to your needs. So please don't complain you're not interesting in top Transaction Dates.