Request: Query Function for Top 3 or 10 Sales Qty in one list/table in Google Sheets
Budget: $30 – $250 USD
Please only use query function. No script no pivot table.
Only Query Function(no more than 2), do not use script or pivot table
Data: 1) manually inputted and changeable product choice list 2) Jan to June sales and inventory data
Output/Result: sales analysis data into table in 1 sheet [all within 1 workbook]
Input/Data sheet:
Manual input list sheet
= the query function will only select the products inputted on the list in this sheet, from the other 12 data sheets, as data input to be analysed/processed.
= the list is manually inputted and will be changed whenever we want to analyse other products/items.
= list starts from row 2, will not be including header at row 1
= example, a list titled ‘BossA’s requested item’ that includes Aus Apple, Nz Apple.
US Apple from monthly sales and inventory data sheets will not be included nor processed by the query function.
= list will include up to 250 items in actual use.
Jan to Jun sales data sheets (6sheets)
= will go up to 1500 products in actual use.
= product may not be present in all the sales data sheet.
= example: US Apple was not bought by any one in May, the item will not show up in May Sales data sheet.
Jan to Jun stock/inventory data sheets (6sheets)
= will go up to 1500 products in actual use.
= product may not be present in all the stock/inventory data sheet.
= example: Aus Apple was sold out in Apr but not restocked until June, the item will not show up in May Stock data sheet.
Output/Result of Query Function: Table list
Top 10 or top 3 products with units sold in 6 months
= sum of total units sold in 6 months for each products, list out top 10, ordeered by most to least
= if the manual input list has equal or more than 10 products, list 10, if less than 10, list 3
Example formula :
units sold of Aus Apple Jan+Feb+Mac+Apr+May+Jun
Repeat for all product in the manual input list, compare and list top 10 or 3
The table will include top listed items’ respective item code, item name.
The table will include top listed items’ respective total $ value sold over 6 months, the formula is similar to total units sold, difference is just to use or calculate $ value instead of quantity.
The table will include top listed items’ respective % of product sales $ value in 6 months over total product sales $ value in 6months(only all products of the manual input list).
Example formula:
TotalSales$ = sum of all $ value of all product from the manual input list from Jan to Jun
itemSales$ = sum of $ value of one of the top 3 or 10 from Jan to Jun
% = itemSales$/TotalSales$
The table will include top listed items’ respective Jun stock number/unit
= as stated above, a product may not appear in one of the month’s stock data sheet if it’s quantity is 0 and not restocked.
= put 0 if product is not present in Jun stock sheet.
The table will include top listed items’ respective Jun stock value $
= stock value is Jun unit quantity x unit price$
= unit price$ is not listed out in any data sheet and needs to be calculated separately
= unit price$ = item sales$(Jan to Jun) / quantity(Jan to Jun)
Please write the query function for BossA’s requested item.
https://docs.google.com/spreadsheets/d/1jXfD2dFXogkopcmkD5hFiL8Fs1vIVQaKyN7HWBi41Kg/edit?usp=sharing
Only Query Function(no more than 2), do not use script or pivot table
Data: 1) manually inputted and changeable product choice list 2) Jan to June sales and inventory data
Output/Result: sales analysis data into table in 1 sheet [all within 1 workbook]
Input/Data sheet:
Manual input list sheet
= the query function will only select the products inputted on the list in this sheet, from the other 12 data sheets, as data input to be analysed/processed.
= the list is manually inputted and will be changed whenever we want to analyse other products/items.
= list starts from row 2, will not be including header at row 1
= example, a list titled ‘BossA’s requested item’ that includes Aus Apple, Nz Apple.
US Apple from monthly sales and inventory data sheets will not be included nor processed by the query function.
= list will include up to 250 items in actual use.
Jan to Jun sales data sheets (6sheets)
= will go up to 1500 products in actual use.
= product may not be present in all the sales data sheet.
= example: US Apple was not bought by any one in May, the item will not show up in May Sales data sheet.
Jan to Jun stock/inventory data sheets (6sheets)
= will go up to 1500 products in actual use.
= product may not be present in all the stock/inventory data sheet.
= example: Aus Apple was sold out in Apr but not restocked until June, the item will not show up in May Stock data sheet.
Output/Result of Query Function: Table list
Top 10 or top 3 products with units sold in 6 months
= sum of total units sold in 6 months for each products, list out top 10, ordeered by most to least
= if the manual input list has equal or more than 10 products, list 10, if less than 10, list 3
Example formula :
units sold of Aus Apple Jan+Feb+Mac+Apr+May+Jun
Repeat for all product in the manual input list, compare and list top 10 or 3
The table will include top listed items’ respective item code, item name.
The table will include top listed items’ respective total $ value sold over 6 months, the formula is similar to total units sold, difference is just to use or calculate $ value instead of quantity.
The table will include top listed items’ respective % of product sales $ value in 6 months over total product sales $ value in 6months(only all products of the manual input list).
Example formula:
TotalSales$ = sum of all $ value of all product from the manual input list from Jan to Jun
itemSales$ = sum of $ value of one of the top 3 or 10 from Jan to Jun
% = itemSales$/TotalSales$
The table will include top listed items’ respective Jun stock number/unit
= as stated above, a product may not appear in one of the month’s stock data sheet if it’s quantity is 0 and not restocked.
= put 0 if product is not present in Jun stock sheet.
The table will include top listed items’ respective Jun stock value $
= stock value is Jun unit quantity x unit price$
= unit price$ is not listed out in any data sheet and needs to be calculated separately
= unit price$ = item sales$(Jan to Jun) / quantity(Jan to Jun)
Please write the query function for BossA’s requested item.
https://docs.google.com/spreadsheets/d/1jXfD2dFXogkopcmkD5hFiL8Fs1vIVQaKyN7HWBi41Kg/edit?usp=sharing