Query Function List Top 3 or 10 Sales Qty
Budget: $10 – $30 USD
Do not bid if you think it cant be done with only 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/inventory data — Output 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/inventory data — Output 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
Related categories:
Google Sheets