Number of units sold within 3 days of going live

[Jacks18928]

Hi


I have a data sheet with below columns which I pull into a pivot table

- LIVE date
- Product number
- Initial stock allocation
- Current stock allocation

My boss wants a calculation which highlights best selling product within a 3 day window of going LIVE

Any ideas regarding formula to drive this?

Cheers

[FDibbins]

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[Jacks18928]

attachment

[congnt92]

Hi Jacks
Since you're using pivot then I would remind you that pivot supports filter "top 10" value.
In cell Row labels, choose value filters >> top 10.
Capture.PNG

[Jacks18928]

Hi
I need to calculate how many units have been sold / initial stock allocation within 7 days of the product going LIVE

[congnt92]

Hi Jacks,
My mistake. I'm confused your question: best selling "within" 3 days vs "top 3".
--
Your report is for one day (8/31/2018). Plz upload your workbook with "fully" report.

[Jacks18928]

Thanks for helping, worksheet attached

I need to identify products that are selling fast within 7 days of it being launched only.

Units / by initial stock but I am struggling to apply filter for the date range

Cheers

[congnt92]

Hi Jacks
I'm sorry but it seems that you don't understand my post #6 correctly.
You need to identify the best selling within 7 days but there is only a live date (8/31/2018) in your workbook.
And since 1 SKU exists 1 time so your goal could be done by just sort value of UNITS SOLD column from largest to smallest.

[congnt92]

Hi Jacks
I uploaded my testing file. Plz check to make sure that I understand your question correctly.
I highly recommend using pivot table for your task but if you still want a formula then check the columns (F:H - with helper columns) or just column I (without helper column needed).
Input your range in L2:M2.
Plz note that using a formula with large data will cause a lag time of several seconds.
HTH.

[Jacks18928]

Hey,
I see what you have done here, looks good
I need the units sold divided the initial stock volume not multiplied.

[congnt92]

So if you use pivot table then you need to add a column to calc units sold per initial stock volume.
If you want to use formula then i will edit it tomorrow. It's is 12pm in my country and i'm on my phone now.

[congnt92]

Hi Jacks
Still I recommend using pivot table for your report. To do that you must first add a column to calc units sold per initial stock volume. After pivot table was created, you sort Z-A column "Sum of UNITS SOLD per INITIAL STOCK". (Change your range of date from timeline)
If you don't want to add a helper column then you can you use this formula in column F (Non-CSE formula)

Please Login or Register  to view this content.

Or shorter but CSE (ARRAY) formula (Finish by pressing Ctrl Shift Enter)

Please Login or Register  to view this content.

These formulas even could be shorter if the INITIAL STOCK of each SKU is not change. (Such as, SKU WF121 always has 10 INITIAL STOCK ...)

P.S: I still have to work with my test workbook since you didn't upload your daily report
HTH.