Scenario
The ranking of the item based on sold quantity.
Please make sure that the ranking of the saved search is based on the criteria mentioned.
The item is initially sorted based on the quantity sold.
If the unit sold is same for few SKUs then rank them by
Then it is sorted based on the quantity available which is more than 2.
Next the item with A, B status 2 and less than 2 stock available is considered.
Then the item with S status 2 and less than 2 stock available Third
With in these above sections rank them by Brand
Brand:
Bosch
LG Appliance
GE Appliance
GE Profile
Cafe
Samsung
Thermador
Monogram
Other Brands
Lower to a higher price (Special price) within the brands above.
Solution
Add Search Title
Criteria> Standard
Inactive: False
Transaction: Date: is within the previous one half.
Transaction: Type: is Sales Order
Transaction: Status: is Billed
Results
Field: Internal ID , Summary Type: Group
Field: Name , Summary Type: Maximum
Field: Transaction:Quantity , Summary Type: Sum
Field: Formula (Numeric), Summary Type: Maximum, CASE WHEN {quantityavailable}>2 THEN {quantityavailable} ELSE 0 END, Label: Qty Available > 2
Field: Formula (Numeric), Summary Type: Maximum, Formula: CASE WHEN {custitem_aha_item_status}IN(‘A’,’B’) AND {quantityavailable}<=2 THEN {quantityavailable} ELSE 0 END, Label: Status A,B Qty
Field: Formula (Numeric), Summary Type: Maximum, Formula: CASE WHEN {custitem_aha_item_status}IN(‘S’) AND {quantityavailable}<=2 THEN {quantityavailable} ELSE 0 END, Label: Status S Qty
Field: Brand(Custom), Summary Type: Maximum
Field: Sales Price(Custom), Summary Type: Maximum
Field: Formula (Numeric), Summary Type: Maximum, Formula: ROW_NUMBER() OVER ( ORDER BY SUM( {transaction.quantity}) DESC, MAX(CASE WHEN {quantityavailable} > 2 THEN {quantityavailable} ELSE 0 END) DESC, MAX(CASE WHEN {custitem_aha_item_status} IN (‘A’, ‘B’) AND {quantityavailable} <= 2 THEN {quantityavailable} ELSE 0 END) DESC, MAX(CASE WHEN {custitem_aha_item_status} IN (‘S’) AND {quantityavailable} <= 2 THEN {quantityavailable} ELSE 0 END) DESC, MAX(CASE WHEN {cseg_aha_brand}=’Bosch’ THEN {custitem_aha_sales_price} END) ASC, MAX(CASE WHEN {cseg_aha_brand}=’LG Appliances’ THEN {custitem_aha_sales_price} END) ASC, MAX(CASE WHEN {cseg_aha_brand}=’GE Appliances’ THEN {custitem_aha_sales_price} END) ASC, MAX(CASE WHEN {cseg_aha_brand}=’GE Profile’ THEN {custitem_aha_sales_price} END) ASC, MAX(CASE WHEN {cseg_aha_brand}=’Cafe’ THEN {custitem_aha_sales_price} END) ASC, MAX(CASE WHEN {cseg_aha_brand}=’Samsung Appliances’ THEN {custitem_aha_sales_price} END) ASC, MAX(CASE WHEN {cseg_aha_brand}=’Thermador’ THEN {custitem_aha_sales_price} END) ASC, MAX(CASE WHEN {cseg_aha_brand}=’Monogram’ THEN {custitem_aha_sales_price} END) ASC, MAX(CASE WHEN {cseg_aha_brand}NOT IN(‘Bosch’,’LG Appliances’, ‘GE Appliances’,’GE Profile’,’Cafe’,’Samsung Appliances’,’Thermador’,’Monogram’) THEN {custitem_aha_sales_price} END) ASC)
Label: Final Ranking
Sort by: Transaction Quantity, Check Descending
Sort by: Formula (Numeric), Check Descending
Please Note:
Special price = Sales Prices in item record.
We need to add the other columns in addition to the ranking columns for the smooth functioning of the saved search.