Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I've got a following table shows last 24 months monthly sales. (Just pick 1 product and 1 branch for demo)
Rolling 12 Months Flag | Calendar Date | Branch ID | Product ID | sum([Financial Quantity Sold]) |
1 | 1/10/2023 | 201 | 10000101 | 4747 |
1 | 1/9/2023 | 201 | 10000101 | 1110 |
1 | 1/8/2023 | 201 | 10000101 | 2846 |
1 | 1/7/2023 | 201 | 10000101 | 1361 |
1 | 1/6/2023 | 201 | 10000101 |
2219 |
1 | 1/5/2023 | 201 | 10000101 | 2305 |
1 | 1/4/2023 | 201 | 10000101 | 1833 |
1 | 1/3/2023 | 201 | 10000101 | 2379 |
1 | 1/2/2023 | 201 | 10000101 | 2500 |
1 | 1/1/2023 | 201 | 10000101 | 3121 |
1 | 1/12/2022 | 201 | 10000101 | 2511 |
1 | 1/11/2022 | 201 | 10000101 | 3235 |
0 | 1/10/2022 | 201 | 10000101 | 3766 |
0 | 1/9/2022 | 201 | 10000101 | 3389 |
0 | 1/8/2022 | 201 | 10000101 | 2857 |
0 | 1/7/2022 | 201 | 10000101 | 3678 |
0 | 1/6/2022 | 201 | 10000101 | 1265 |
0 | 1/5/2022 | 201 | 10000101 | 1616 |
0 | 1/4/2022 | 201 | 10000101 | 4083 |
0 | 1/3/2022 | 201 | 10000101 | 3348 |
0 | 1/2/2022 | 201 | 10000101 | 3694 |
0 | 1/1/2022 | 201 | 10000101 | 2182 |
0 | 1/12/2021 | 201 | 10000101 | 529 |
0 | 1/11/2021 | 201 | 10000101 | 373 |
0 | 1/10/2021 | 201 | 10000101 | 200 |
Rolling 12 months Flag = 1 help to filter last 12 months only and I am trying to write a formula that sum up the top 3 monthly sales for each product by branch. So I am looking to build:
Branch ID | Product ID | Top 3 Months sales |
201 | 10000101 | 11103 |
In last 12 months for this product and branch, the highest 3 months sales are (4747 + 3235 + 3121) = 11103.
I try to use the following formula but not work:
RangeSum(
Aggr(
Above(
Sum({<[Rolling 12 Months Flag] = {"1"}>} [Financial Quantity Sold]),
0,
RowNo() <= 3
),
[Branch ID], [Product ID], [Calendar Date]
)
)
Anyone can help me out?
Thanks in advance.
Gavin
Maybe like this:
sum({<CalendarDate = {"=Rank(sum({<Rolling12MonthsFlag={1}>}FinancialQuantitySold))<4"}>} FinancialQuantitySold)
Many Thanks.
I have a play with the logic and the formula only working when filter down to a branch for a product. But if I do not put any filter and leave it as a flat table with all branches and products,the formula is not working.
Formula used:
sum({<[Calendar Date] = {"=Rank(sum({<[Rolling 12 Months Flag]={1}>}[Financial Quantity Sold]),4)<4"}>} [Financial Quantity Sold])
P.S: added mode 4 in Rank() as I only want the top 3 numbers of Qty Sold.
An example here when I've only filtered product: 1353262 only and eyeball on branch: 146.
Left-hand table are using the formula that gives me 7918. However, if you look at the right hand-side table, the rank() do flag correctly for the highest top 3 month's sale and they should sum up to 11103.
If I add another filter at a specific branch, the formula do give me the right number~
Any thought?
Tried: sum(aggr(sum({<[Calendar Date] = {"=Rank(sum({<[Rolling 12 Months Flag]={1}>}[Financial Quantity Sold]),4)<4"}>} [Financial Quantity Sold]),[Branch ID]))
return me the same result.
Cheers
Gavin