Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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).