| Vendor Name |
Last 6 Months Sales |
Cumulative Sum |
Total (Cumulative Sum) |
| |
total = 5500 |
|
|
| A |
1200 |
1200 |
1200/5500 |
| B |
2500 |
3700 |
3700/5500 |
| C |
800 |
4500 |
4500/5500 |
| D |
1000 |
5500 |
5500/5500 |
I get these calculations in front end but I need only top vendors according to Cumulative Sum and Total Cumulative Sum in back end.
Last 6 Months Sales == Sum({<Date = {"<=$(vCurrentDay)"},Date= {">=$(v6MonthsAgo)"},[SA Status]={' Receipt'}>} [Sales])
Cumulative Sum = RangeSum(Above(TOTAL Sum({$<Date = {"<=$(vCurrentDay)"},Date= {">=$(v6MonthsAgo)"},[SA Status]={' Receipt'}>} [Sales]), 0, RowNo(TOTAL)))
Requirement:
The above one is sample data in table to explain the calculations, I need top vendors and all calculations need to be in Load script . the requirement is :
Look at Vendor's sale in the last 6 months and calculate the total at the Vendor level, order that table from high to low. Then create a cumulative sum (so Vendor #1 shows Vendor 1 sales, Venodr #2 shows Vendor #1 AND Vendor #2 sales, and so on). Whenever [Cumulative Sum] < Sum(Total [Cumulative Sum])*.8 then [Top Vendor] = "Y"
1- how to get Sum(sales ) (Asc) order ------------as column 2
2- how to get cumulative SUM---------------------as column 3
3- how to get SUM(Total Cumulative SUM)-----as column 4
4- if Statement to get top Vendor = Y
I tried the aggr and RangeSUM functions in load editor to get the top Vendors.
Output Table: Needs to show top Vendors according to above requirement , I need to show Last 6 months sales (high to low).
