Dynamic achievement and rank calculation for terittory_sales_achievement

Information:

 

Rank and achievement of each team is vital, since it shows the performance of each team and highlights areas where they excel or need improvement, enabling better decision-making and resource allocation for achieving sales targets.

 

Requirement: 

 

Develop a Databricks PySpark script to create a dropdown widget in Databricks with two options: "Territory level" and "District level." When the user selects "Territory level," and create a Spark SQL code which should calculate territory_achievement using the formula sum(actual_value) / sum(target_value) for each combination of Team_name, territory_id, brand_name, and cycle_name. The script should also rank Team_name based on territory_id, brand_name, cycle_name, and territory_achievement. Similarly, if the user selects "District level," perform the same calculation and ranking using district_id instead of territory_id.

 

Unity catalog information: purgo_playground.territory_sales_achievement_raw

 

Expected output: Databricks PySpark/Spark SQL script.