🎓 QlikSense Analytics Practice Assignment

Business Scenario

📊 Company Background

Company Name: TechRetail Solutions Inc.
Industry: Consumer Electronics Retail
Business Model: Multi-channel retail (Online + Physical Stores)
Fiscal Year: January - December

📈 Current Situation

TechRetail Solutions has been operating for 3 years across 5 regions (North, South, East, West, Central). The company sells electronics through:

• 15 physical retail stores
• 1 e-commerce platform
• Corporate bulk sales program

The CFO has requested a comprehensive Sales Performance Dashboard to answer critical business questions for Q4 2024 planning.

🎯 Business Objectives

1. Revenue Analysis: Understand sales patterns by product, region, and channel
2. Customer Insights: Identify customer purchasing behavior and loyalty
3. Inventory Management: Optimize stock levels based on sales velocity
4. Performance Tracking: Monitor sales team effectiveness
5. Trend Analysis: Identify seasonal patterns and forecast demand

📊 Available Data Sources

You will work with 3 main data files (QVD format):

1. Sales_Transactions.qvd - Daily sales records
2. Products_Master.qvd - Product catalog and pricing
3. Customers_Master.qvd - Customer demographics

Setup Instructions

Step 1: Create Folder Structure

Create the following folders on your C: drive:

C:\Practice\
    ├── DataFiles\              (Source QVD files)
    ├── DataFiles\Transformed\  (Your transformed QVD files)
    └── Scripts\                (Your QlikSense scripts)

Step 2: Run Data Generator Script

1. Open QlikSense Desktop
2. Create a new app called "Practice_DataGenerator"
3. Copy and paste the Data Generator Script (provided below)
4. Click "Load Data"
5. Verify that 3 QVD files are created in C:\Practice\DataFiles\

Step 3: Create Your Working App

1. Create a new app called "TechRetail_Sales_Analysis"
2. This is where you'll complete all the transformation tasks
3. Save frequently!

Data Generator Script

📝 Instructions:

Copy this entire script into QlikSense Desktop Data Load Editor and run it once.

//====================================================================
// TECHRETAIL SOLUTIONS - PRACTICE DATA GENERATOR
// Purpose: Generates realistic sales data for training exercise
// Author: Training Team
// Date: January 2026
//====================================================================

//--- CONFIGURATION ---
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00';
SET DateFormat='DD-MM-YYYY';

LET vDataPath = 'C:\Practice\DataFiles\';

//--- ENSURE DIRECTORY EXISTS (Manual creation required) ---
// Please create C:\Practice\DataFiles\ folder before running this script

//====================================================================
// GENERATE PRODUCTS MASTER DATA
//====================================================================

[Products_Master]:
LOAD * INLINE [
Product_ID, Product_Name, Category, Sub_Category, Brand, Cost_Price, Retail_Price, Reorder_Level, Supplier_ID
P001, Smartphone X1, Electronics, Mobile Phones, TechBrand, 450, 699, 50, SUP01
P002, Smartphone X2 Pro, Electronics, Mobile Phones, TechBrand, 650, 999, 40, SUP01
P003, Laptop Ultra 15, Electronics, Laptops, CompuBrand, 800, 1299, 30, SUP02
P004, Laptop Pro 17, Electronics, Laptops, CompuBrand, 1200, 1899, 20, SUP02
P005, Tablet T10, Electronics, Tablets, TechBrand, 250, 399, 60, SUP01
P006, Smartwatch S1, Electronics, Wearables, FitBrand, 150, 249, 100, SUP03
P007, Wireless Earbuds, Electronics, Audio, SoundBrand, 50, 89, 200, SUP04
P008, Bluetooth Speaker, Electronics, Audio, SoundBrand, 80, 149, 80, SUP04
P009, USB-C Cable, Accessories, Cables, GenericBrand, 5, 15, 500, SUP05
P010, Phone Case Premium, Accessories, Cases, GenericBrand, 8, 25, 300, SUP05
P011, Wireless Mouse, Electronics, Peripherals, CompuBrand, 20, 39, 150, SUP02
P012, Mechanical Keyboard, Electronics, Peripherals, CompuBrand, 60, 119, 80, SUP02
P013, 4K Monitor 27", Electronics, Monitors, ViewBrand, 300, 499, 40, SUP06
P014, Webcam HD Pro, Electronics, Peripherals, ViewBrand, 45, 89, 100, SUP06
P015, External SSD 1TB, Electronics, Storage, StorageBrand, 90, 159, 120, SUP07
P016, Power Bank 20K, Accessories, Power, ChargeBrand, 25, 49, 200, SUP08
P017, Screen Protector, Accessories, Protection, GenericBrand, 3, 12, 400, SUP05
P018, HDMI Cable 2m, Accessories, Cables, GenericBrand, 8, 20, 250, SUP05
P019, Gaming Headset, Electronics, Audio, SoundBrand, 70, 129, 60, SUP04
P020, Portable Charger, Accessories, Power, ChargeBrand, 15, 35, 180, SUP08
] (delimiter is ',');

STORE [Products_Master] INTO [$(vDataPath)Products_Master.qvd] (qvd);
DROP TABLE [Products_Master];

//====================================================================
// GENERATE CUSTOMERS MASTER DATA
//====================================================================

[Customers_Master]:
LOAD * INLINE [
Customer_ID, Customer_Name, Customer_Type, Region, City, State, Join_Date, Email, Phone, Loyalty_Tier
C0001, John Smith, Individual, North, Boston, MA, 15-01-2023, [email protected], 555-0101, Gold
C0002, Sarah Johnson, Individual, North, New York, NY, 22-03-2023, [email protected], 555-0102, Silver
C0003, Michael Brown, Individual, South, Atlanta, GA, 10-02-2023, [email protected], 555-0103, Bronze
C0004, Emily Davis, Individual, East, Philadelphia, PA, 05-04-2023, [email protected], 555-0104, Gold
C0005, David Wilson, Individual, West, Los Angeles, CA, 18-01-2023, [email protected], 555-0105, Platinum
C0006, Lisa Anderson, Individual, Central, Chicago, IL, 25-02-2023, [email protected], 555-0106, Silver
C0007, James Martinez, Individual, South, Houston, TX, 12-03-2023, [email protected], 555-0107, Bronze
C0008, Jennifer Taylor, Individual, West, San Francisco, CA, 08-05-2023, [email protected], 555-0108, Gold
C0009, Robert Thomas, Individual, North, Boston, MA, 30-01-2023, [email protected], 555-0109, Silver
C0010, Mary Jackson, Individual, East, Washington, DC, 14-04-2023, [email protected], 555-0110, Bronze
C0011, Tech Corp Inc, Corporate, Central, Chicago, IL, 20-01-2023, [email protected], 555-0201, Platinum
C0012, Digital Solutions LLC, Corporate, West, Seattle, WA, 15-02-2023, [email protected], 555-0202, Gold
C0013, Metro University, Corporate, North, New York, NY, 28-02-2023, [email protected], 555-0203, Gold
C0014, Healthcare Plus, Corporate, South, Miami, FL, 10-03-2023, [email protected], 555-0204, Silver
C0015, Finance Group, Corporate, East, Philadelphia, PA, 22-03-2023, [email protected], 555-0205, Platinum
C0016, Patricia White, Individual, Central, St Louis, MO, 05-06-2023, [email protected], 555-0111, Bronze
C0017, Christopher Lee, Individual, West, Denver, CO, 18-06-2023, [email protected], 555-0112, Silver
C0018, Amanda Hall, Individual, South, Dallas, TX, 22-07-2023, [email protected], 555-0113, Gold
C0019, Daniel Young, Individual, North, Portland, ME, 15-08-2023, [email protected], 555-0114, Bronze
C0020, Nancy King, Individual, East, Baltimore, MD, 30-08-2023, [email protected], 555-0115, Silver
C0021, StartUp Ventures, Corporate, West, San Jose, CA, 12-04-2023, [email protected], 555-0206, Silver
C0022, Retail Chain Co, Corporate, Central, Indianapolis, IN, 25-04-2023, [email protected], 555-0207, Gold
C0023, Manufacturing Ltd, Corporate, South, Charlotte, NC, 08-05-2023, [email protected], 555-0208, Silver
C0024, Education Board, Corporate, North, Buffalo, NY, 19-05-2023, [email protected], 555-0209, Bronze
C0025, Legal Associates, Corporate, East, Richmond, VA, 02-06-2023, [email protected], 555-0210, Gold
] (delimiter is ',');

STORE [Customers_Master] INTO [$(vDataPath)Customers_Master.qvd] (qvd);
DROP TABLE [Customers_Master];

//====================================================================
// GENERATE SALES TRANSACTIONS (2024 Data)
//====================================================================

[Sales_Transactions]:
LOAD
    RowNo() as Transaction_ID,
    Date(Date#('01-01-2024', 'DD-MM-YYYY') + Floor(Rand()*365)) as Transaction_Date,
    Time(Floor(Rand()*86400)/86400, 'hh:mm:ss') as Transaction_Time,
    Pick(Ceil(Rand()*3), 'Store', 'Online', 'Corporate') as Sales_Channel,
    Pick(Ceil(Rand()*15), 
        'Store_N01', 'Store_N02', 'Store_N03',
        'Store_S01', 'Store_S02', 'Store_S03',
        'Store_E01', 'Store_E02', 'Store_E03',
        'Store_W01', 'Store_W02', 'Store_W03',
        'Store_C01', 'Store_C02', 'Store_C03') as Store_ID,
    'P' & Text(Num(Ceil(Rand()*20), '000')) as Product_ID,
    'C' & Text(Num(Ceil(Rand()*25), '0000')) as Customer_ID,
    Ceil(Rand()*5) as Quantity,
    Pick(Ceil(Rand()*10), 
        'EMP001', 'EMP002', 'EMP003', 'EMP004', 'EMP005',
        'EMP006', 'EMP007', 'EMP008', 'EMP009', 'EMP010') as Sales_Rep_ID,
    Pick(Ceil(Rand()*4), 'Cash', 'Credit Card', 'Debit Card', 'Digital Wallet') as Payment_Method,
    If(Rand() > 0.95, 'Yes', 'No') as Is_Returned,
    If(Rand() > 0.9, 'Yes', 'No') as Has_Warranty,
    If(Rand() > 0.85, Ceil(Rand()*20), 0) as Discount_Percent
AUTOGENERATE 5000;

STORE [Sales_Transactions] INTO [$(vDataPath)Sales_Transactions.qvd] (qvd);
DROP TABLE [Sales_Transactions];

//====================================================================
// DATA GENERATION COMPLETE
//====================================================================

[Generation_Log]:
LOAD * INLINE [
File_Name, Records_Generated, Status
Products_Master.qvd, 20, Success
Customers_Master.qvd, 25, Success
Sales_Transactions.qvd, 5000, Success
] (delimiter is ',');

TRACE ====================================================================;
TRACE DATA GENERATION COMPLETED SUCCESSFULLY;
TRACE ====================================================================;
TRACE Location: C:\Practice\DataFiles\;
TRACE Files Created:;
TRACE   - Products_Master.qvd (20 products);
TRACE   - Customers_Master.qvd (25 customers);
TRACE   - Sales_Transactions.qvd (5000 transactions);
TRACE ====================================================================;
TRACE Next Step: Create new app "TechRetail_Sales_Analysis" for exercises;
TRACE ====================================================================;

After running this script, verify: ✅ 3 QVD files exist in C:\Practice\DataFiles\
✅ File sizes are reasonable (not 0 KB)
✅ Generation Log shows "Success" for all files

Part 1: Data Extraction & Understanding

🎯 Objective

Load the source QVD files and understand the data structure.

📝 Task 1.1: Load Products Master Data

Instructions:

1. Create a new script tab called "01_Load_Products"
2. Load Products_Master.qvd from source folder
3. Create calculated fields as specified below

Business Requirements:

• Calculate Profit Margin % = ((Retail_Price - Cost_Price) / Retail_Price) * 100
• Create Price Category based on Retail_Price:
  • Budget: < $50
  • Mid-Range: $50 - $500
  • Premium: $501 - $1000
  • Luxury: > $1000
• Create Stock Status based on Reorder_Level:
  • Critical: < 50
  • Low: 50 - 100
  • Normal: 101 - 200
  • High: > 200

Expected Output:

[Products]:
LOAD 
    Product_ID,
    Product_Name,
    Category,
    Sub_Category,
    Brand,
    Cost_Price,
    Retail_Price,
    // YOUR CODE HERE: Calculate Profit Margin %
    // YOUR CODE HERE: Create Price Category
    Reorder_Level,
    // YOUR CODE HERE: Create Stock Status
    Supplier_ID
FROM [C:\Practice\DataFiles\Products_Master.qvd] (qvd);

💡 Hint: Review Section 7.1 (IF Function) in the glossary for nested conditions.

📝 Task 1.2: Load Customers Master Data

Instructions:

1. Create a new script tab called "02_Load_Customers"
2. Load Customers_Master.qvd
3. Add calculated fields

Business Requirements:

• Calculate Customer Age (Days) = Today - Join_Date
• Create Customer Segment based on Customer_Type and Loyalty_Tier:
  • VIP Corporate: Corporate + Platinum
  • Key Corporate: Corporate + Gold
  • Regular Corporate: Corporate + (Silver or Bronze)
  • Premium Individual: Individual + (Platinum or Gold)
  • Standard Individual: Individual + (Silver or Bronze)
• Extract Region Code = First letter of Region (N, S, E, W, C)
• Create Is_Active_Customer flag = 'Yes' if Join_Date within last 180 days, else 'No'

Expected Output:

[Customers]:
LOAD 
    Customer_ID,
    Customer_Name,
    Customer_Type,
    Region,
    City,
    State,
    DATE(Date#(Join_Date, 'DD-MM-YYYY'), 'DD-MM-YYYY') as Join_Date,
    // YOUR CODE HERE: Calculate Customer Age Days
    // YOUR CODE HERE: Create Customer Segment
    // YOUR CODE HERE: Extract Region Code
    // YOUR CODE HERE: Create Is_Active_Customer flag
    Email,
    Phone,
    Loyalty_Tier
FROM [C:\Practice\DataFiles\Customers_Master.qvd] (qvd);

💡 Hint:

• Review Section 5.4 (TODAY function)
• Review Section 4.4 (LEFT function) for extracting first character
• Review Section 7.1 (IF) for nested conditions

📝 Task 1.3: Load Sales Transactions

Instructions:

1. Create a new script tab called "03_Load_Sales"
2. Load Sales_Transactions.qvd
3. DO NOT add calculations yet (we'll do this in Part 2)

Expected Output:

[Sales_Raw]:
LOAD 
    Transaction_ID,
    DATE(Transaction_Date, 'DD-MM-YYYY') as Transaction_Date,
    Transaction_Time,
    Sales_Channel,
    Store_ID,
    Product_ID,
    Customer_ID,
    Quantity,
    Sales_Rep_ID,
    Payment_Method,
    Is_Returned,
    Has_Warranty,
    Discount_Percent
FROM [C:\Practice\DataFiles\Sales_Transactions.qvd] (qvd);

📝 Task 1.4: Create Mapping Tables

Instructions:

1. Create a new script tab called "04_Mappings"
2. Create mapping tables for efficient lookups

Business Requirements: Create mappings for:

1. Product_ID → Retail_Price
2. Product_ID → Cost_Price
3. Product_ID → Product_Name
4. Product_ID → Category
5. Customer_ID → Customer_Type
6. Customer_ID → Region

Expected Output:

//--- Product Mappings ---
[Map_Product_RetailPrice]:
MAPPING LOAD 
    Product_ID,
    Retail_Price
RESIDENT Products;

[Map_Product_CostPrice]:
// YOUR CODE HERE

[Map_Product_Name]:
// YOUR CODE HERE

// Continue for all required mappings...

💡 Hint: Review Section 1.2 (MAPPING LOAD) and Section 8.1 (APPLYMAP) in glossary.

Part 2: Data Transformation Tasks

🎯 Objective

Transform raw sales data with business logic and create fact table.

📝 Task 2.1: Enrich Sales with Product Information

Instructions:

1. Create a new script tab called "05_Sales_Enrichment"
2. Load Sales_Raw and apply mappings to add product information
3. Calculate sales amounts

Business Requirements:

• Add Product_Name, Category, Retail_Price, Cost_Price using APPLYMAP
• Calculate Gross_Amount = Quantity × Retail_Price
• Calculate Discount_Amount = Gross_Amount × (Discount_Percent / 100)
• Calculate Net_Amount = Gross_Amount - Discount_Amount
• Calculate Cost_Amount = Quantity × Cost_Price
• Calculate Profit_Amount = Net_Amount - Cost_Amount
• Calculate Profit_Margin_% = (Profit_Amount / Net_Amount) × 100

Expected Output:

[Sales_Enriched]:
LOAD 
    Transaction_ID,
    Transaction_Date,
    Transaction_Time,
    Sales_Channel,
    Store_ID,
    Product_ID,
    APPLYMAP('Map_Product_Name', Product_ID, 'Unknown') as Product_Name,
    // YOUR CODE HERE: Add other product fields via APPLYMAP
    Customer_ID,
    Quantity,
    // YOUR CODE HERE: Calculate all amounts
    Sales_Rep_ID,
    Payment_Method,
    Is_Returned,
    Has_Warranty,
    Discount_Percent
RESIDENT Sales_Raw;

DROP TABLE Sales_Raw;

💡 Hint: Review Section 8.1 (APPLYMAP) for lookups.

📝 Task 2.2: Add Customer Information

Instructions: Continue enriching Sales_Enriched table with customer data.

Business Requirements:

• Add Customer_Type, Region, Loyalty_Tier using APPLYMAP or LEFT JOIN
• Create Customer_Channel =
  • If Customer_Type = 'Corporate' → 'B2B'
  • If Sales_Channel = 'Online' → 'B2C Online'
  • Else → 'B2C Retail'

Expected Output:

LEFT JOIN (Sales_Enriched)
LOAD 
    Customer_ID,
    Customer_Type,
    Region,
    Loyalty_Tier,
    Customer_Segment
RESIDENT Customers;

// Then add Customer_Channel as calculated field
// YOUR CODE HERE

💡 Hint: Review Section 2.1 (LEFT JOIN).

📝 Task 2.3: Create Date Dimension

Instructions:

1. Create a new script tab called "06_Calendar"
2. Build a calendar table from Sales transaction dates

Business Requirements: Create calendar with following fields:

• Date
• Year
• Month (number)
• Month_Name (JAN, FEB, MAR...)
• Quarter (Q1, Q2, Q3, Q4)
• Year_Month (2024-01, 2024-02...)
• Year_Quarter (2024-Q1, 2024-Q2...)
• Week_Number
• Day_of_Week (Monday, Tuesday...)
• Is_Weekend (Yes/No)
• Fiscal_Quarter (if fiscal year starts in April)

Expected Output:

[Calendar]:
LOAD 
    TempDate as Date,
    Year(TempDate) as Year,
    Month(TempDate) as Month,
    // YOUR CODE HERE: Add all other date fields
LOAD 
    Date(MinDate + IterNo() - 1) as TempDate
WHILE MinDate + IterNo() - 1 <= MaxDate;

LOAD 
    Min(Transaction_Date) as MinDate,
    Max(Transaction_Date) as MaxDate
RESIDENT Sales_Enriched;

💡 Hint:

• Review Section 1.6 (AUTOGENERATE)
• Review Section 5 (all date functions)
• Review Section 12.1 (DUAL) for proper month sorting

📝 Task 2.4: Create Aggregated Tables

Instructions:

1. Create a new script tab called "07_Aggregations"
2. Create summary tables for performance

Business Requirements:

A. Daily Sales Summary:

[Daily_Sales_Summary]:
LOAD 
    Transaction_Date,
    Region,
    Sales_Channel,
    Category,
    COUNT(DISTINCT Transaction_ID) as Transaction_Count,
    SUM(Quantity) as Total_Quantity,
    SUM(Gross_Amount) as Total_Gross,
    SUM(Discount_Amount) as Total_Discount,
    SUM(Net_Amount) as Total_Net,
    SUM(Profit_Amount) as Total_Profit,
    // YOUR CODE HERE: Add AVG calculations
    COUNT(DISTINCT Customer_ID) as Unique_Customers
RESIDENT Sales_Enriched
GROUP BY Transaction_Date, Region, Sales_Channel, Category;

B. Product Performance Summary:

[Product_Performance]:
LOAD 
    Product_ID,
    Product_Name,
    Category,
    // YOUR CODE HERE: Aggregate by product
RESIDENT Sales_Enriched
GROUP BY Product_ID, Product_Name, Category;

C. Customer Purchase Summary:

[Customer_Summary]:
// YOUR CODE HERE: Create customer-level aggregations
// Include: Total purchases, Total amount spent, Avg transaction value, Last purchase date

💡 Hint: Review Section 3.2 (GROUP BY) and Section 9 (all aggregation functions).

📝 Task 2.5: Handle Returns and Adjustments

Instructions: Create a separate returns analysis table.

Business Requirements:

• Filter Sales_Enriched where Is_Returned = 'Yes'
• Calculate return rate by product, category, channel
• Create Return_Amount = Net_Amount (for returned items)
• Flag High_Return_Product if return count > 10

Expected Output:

[Returns_Analysis]:
LOAD 
    Product_ID,
    Product_Name,
    Category,
    COUNT(Transaction_ID) as Return_Count,
    SUM(Net_Amount) as Return_Amount,
    // YOUR CODE HERE: Add more calculations
RESIDENT Sales_Enriched
WHERE Is_Returned = 'Yes'
GROUP BY Product_ID, Product_Name, Category;

📝 Task 2.6: Create Ranked Lists

Instructions: Create top/bottom product rankings.

Business Requirements:

• Top 10 products by revenue
• Top 10 products by quantity sold
• Top 10 products by profit
• Bottom 10 products by profit margin

Expected Output:

[Top_Products_By_Revenue]:
LOAD 
    *,
    RowNo() as Revenue_Rank
;
LOAD 
    Product_ID,
    Product_Name,
    SUM(Net_Amount) as Total_Revenue
RESIDENT Sales_Enriched
GROUP BY Product_ID, Product_Name
ORDER BY SUM(Net_Amount) DESC;

// YOUR CODE HERE: Create other ranking tables

💡 Hint: Review Section 10.3 (ROWNO) and Section 3.3 (ORDER BY).

📝 Task 2.7: Store Transformed Data

Instructions: Save all transformed tables to QVD for future use.

Business Requirements: Store the following tables as QVD in C:\Practice\DataFiles\Transformed\:

1. Sales_Enriched.qvd
2. Daily_Sales_Summary.qvd
3. Product_Performance.qvd
4. Customer_Summary.qvd
5. Returns_Analysis.qvd
6. Calendar.qvd

Expected Output:

LET vTransformedPath = 'C:\Practice\DataFiles\Transformed\';

STORE Sales_Enriched INTO [$(vTransformedPath)Sales_Enriched.qvd] (qvd);
STORE Daily_Sales_Summary INTO [$(vTransformedPath)Daily_Sales_Summary.qvd] (qvd);
// YOUR CODE HERE: Store remaining tables

💡 Hint: Review Section 15.3 (STORE).

Part 3: Advanced Calculations

🎯 Objective

Create complex business metrics and KPIs.

📝 Task 3.1: Calculate Previous Period Comparisons

Instructions: Add previous month sales comparison to Daily_Sales_Summary.

Business Requirements: For each date, calculate:

• Previous_Month_Sales = Total sales from same day previous month
• MoM_Growth_% = ((This Month - Previous Month) / Previous Month) × 100
• YTD_Sales = Cumulative sales from year start to current date

Expected Output:

// Use PEEK or mapping approach
// YOUR CODE HERE

// Alternative: Calculate in chart expressions using Set Analysis

💡 Hint: Review Section 10.1 (PEEK) or use Set Analysis from Section 13.

📝 Task 3.2: Create Customer Segmentation Score

Instructions: Calculate RFM (Recency, Frequency, Monetary) score for customers.

Business Requirements: For each customer:

• Recency_Score (1-5): Days since last purchase
  • 5: < 30 days
  • 4: 30-60 days
  • 3: 61-90 days
  • 2: 91-180 days
  • 1: > 180 days
• Frequency_Score (1-5): Number of transactions
  • 5: > 20 transactions
  • 4: 16-20
  • 3: 11-15
  • 2: 6-10
  • 1: 1-5
• Monetary_Score (1-5): Total spending
  • 5: > $5000
  • 4: $2001-$5000
  • 3: $1001-$2000
  • 2: $501-$1000
  • 1: ≤ $500
• RFM_Score = Concatenate(Recency_Score, Frequency_Score, Monetary_Score)
  • Example: "554" = Very recent, very frequent, high value

Expected Output:

[Customer_RFM]:
LOAD 
    Customer_ID,
    Customer_Name,
    // YOUR CODE HERE: Calculate days since last purchase
    // YOUR CODE HERE: Calculate frequency score
    // YOUR CODE HERE: Calculate monetary score
    // YOUR CODE HERE: Create RFM_Score
RESIDENT Sales_Enriched;

💡 Hint: Use nested IF statements (Section 7.1) and TODAY() function (Section 5.4).

📝 Task 3.3: Sales Velocity Analysis

Instructions: Calculate how fast products are selling.

Business Requirements: For each product:

• Days_On_Market = Days between first sale and last sale
• Total_Units_Sold = Sum of quantity
• Sales_Velocity = Total_Units_Sold / Days_On_Market (units per day)
• Velocity_Category:
  • Fast Moving: > 5 units/day
  • Medium Moving: 1-5 units/day
  • Slow Moving: < 1 unit/day

Expected Output:

[Product_Velocity]:
LOAD 
    Product_ID,
    Product_Name,
    // YOUR CODE HERE
RESIDENT Sales_Enriched
GROUP BY Product_ID, Product_Name;

💡 Hint: Use MIN/MAX dates (Section 9.3) to calculate days on market.

📝 Task 3.4: Create Dynamic Price Optimization Flag

Instructions: Identify products that might benefit from price adjustment.

Business Requirements: Flag products for price review if:

• High inventory (Reorder_Level > 150) AND Low Sales Velocity (< 1 unit/day)
• OR High return rate (> 10% of sales)
• OR Profit margin < 20%

Create Price_Review_Reason field explaining why.

Expected Output:

[Price_Optimization]:
LOAD 
    *,
    If(// YOUR CONDITIONS HERE, 'Yes', 'No') as Needs_Price_Review,
    // YOUR CODE HERE: Add reason
RESIDENT Product_Performance;

Part 4: Visualization Requirements

🎯 Objective

Create a comprehensive dashboard to answer business questions.

📊 Dashboard Layout

Create 4 sheets in QlikSense with the following visualizations:

Sheet 1: Executive Overview

KPI Tiles (Top of page):

1. Total Revenue (current year)

   • Expression: Sum(Net_Amount)
   • Format: Money

2. Total Profit

   • Expression: Sum(Profit_Amount)
   • Format: Money

3. Profit Margin %

   • Expression: Sum(Profit_Amount) / Sum(Net_Amount)
   • Format: Percent

4. Total Transactions

   • Expression: Count(DISTINCT Transaction_ID)

5. Avg Transaction Value

   • Expression: Avg(Net_Amount)
   • Format: Money

Chart 1: Monthly Revenue Trend

• Type: Line Chart
• Dimension: Year_Month (from Calendar)
• Measure 1: Revenue = Sum(Net_Amount)
• Measure 2: Profit = Sum(Profit_Amount)

Chart 2: Revenue by Region (Pie Chart)

• Dimension: Region
• Measure: Sum(Net_Amount)

Chart 3: Revenue by Sales Channel (Bar Chart)

• Dimension: Sales_Channel
• Measure: Revenue, Profit

Chart 4: Top 10 Products by Revenue (Bar Chart)

• Dimension: Product_Name
• Measure: Sum(Net_Amount)
• Limit: Top 10

Sheet 2: Product Analysis

Chart 1: Category Performance (Combo Chart)

• Dimension: Category
• Measure 1 (Bars): Revenue = Sum(Net_Amount)
• Measure 2 (Line): Profit Margin % = Sum(Profit_Amount) / Sum(Net_Amount)

Chart 2: Product Performance Matrix (Scatter Plot)

• Dimension: Product_Name
• X-Axis: Total Quantity Sold
• Y-Axis: Profit Margin %
• Bubble Size: Revenue

Chart 3: Price Category Distribution (Treemap)

• Dimension 1: Price_Category (from Products)
• Dimension 2: Product_Name
• Measure: Revenue

Chart 4: Sales Velocity Analysis (Table)

• Dimensions: Product_Name, Category
• Measures: Sales_Velocity, Velocity_Category, Total_Units_Sold

Filter Pane:

• Category
• Sub_Category
• Brand
• Price_Category

Sheet 3: Customer Insights

Chart 1: Customer Segmentation (Stacked Bar)

• Dimension: Customer_Segment
• Measure 1: Customer Count = Count(DISTINCT Customer_ID)
• Measure 2: Total Revenue = Sum(Net_Amount)

Chart 2: RFM Distribution (Heatmap / Pivot Table)

• Dimension 1: Recency_Score
• Dimension 2: Frequency_Score
• Measure: Count of Customers, colored by Monetary_Score

Chart 3: Customer Lifetime Value (Bar Chart)

• Dimension: Top 20 Customers by Revenue
• Measure: Total Spending

Chart 4: Loyalty Tier Performance

• Dimension: Loyalty_Tier
• Measures:
  • Avg Transaction Value
  • Transaction Count
  • Total Revenue

Chart 5: Region-wise Customer Distribution (Map or Bar)

• Dimension: Region
• Measures: Customer Count, Revenue per Customer

Filter Pane:

• Customer_Type
• Loyalty_Tier
• Region
• Customer_Segment

Sheet 4: Returns & Quality Analysis

KPI Tiles:

1. Total Returns

   • Expression: Count({<Is_Returned={'Yes'}>} Transaction_ID)

2. Return Rate %

   • Expression: Count({<Is_Returned={'Yes'}>} Transaction_ID) / Count(Transaction_ID)

3. Return Amount

   • Expression: Sum({<Is_Returned={'Yes'}>} Net_Amount)

Chart 1: Returns by Category (Bar Chart)

• Dimension: Category
• Measure 1: Return Count
• Measure 2: Return Rate %

Chart 2: Returns Trend (Line Chart)

• Dimension: Month
• Measure: Return Count, Return Amount

Chart 3: Top Products by Returns (Table)

• Dimensions: Product_Name, Category
• Measures: Return Count, Return Amount, Return Rate %
• Sort: Descending by Return Count

Chart 4: Returns by Reason Matrix

• Dimension 1: Category
• Dimension 2: Sales_Channel
• Measure: Return Rate %

Filter Pane:

• Is_Returned
• Category
• Sales_Channel

Solution Hints

💡 Hint Section Organized by Task

Task 1.1 Solution Hints

Profit Margin % Calculation:

((Retail_Price - Cost_Price) / Retail_Price) * 100 as Profit_Margin_Pct

Price Category (Nested IF):

If(Retail_Price < 50, 'Budget',
    If(Retail_Price <= 500, 'Mid-Range',
        If(Retail_Price <= 1000, 'Premium', 'Luxury')
    )
) as Price_Category

Stock Status:

If(Reorder_Level < 50, 'Critical',
    If(Reorder_Level <= 100, 'Low',
        If(Reorder_Level <= 200, 'Normal', 'High')
    )
) as Stock_Status

Task 1.2 Solution Hints

Customer Age Days:

Today() - Date#(Join_Date, 'DD-MM-YYYY') as Customer_Age_Days

Customer Segment (Complex Nested IF):

If(Customer_Type = 'Corporate',
    If(Loyalty_Tier = 'Platinum', 'VIP Corporate',
        If(Loyalty_Tier = 'Gold', 'Key Corporate', 'Regular Corporate')
    ),
    If(Match(Loyalty_Tier, 'Platinum', 'Gold'), 'Premium Individual', 'Standard Individual')
) as Customer_Segment

Region Code:

LEFT(Region, 1) as Region_Code

Is_Active_Customer:

If(Today() - Date#(Join_Date, 'DD-MM-YYYY') <= 180, 'Yes', 'No') as Is_Active_Customer

Task 1.4 Solution Hints

All Mapping Tables:

[Map_Product_RetailPrice]:
MAPPING LOAD Product_ID, Retail_Price RESIDENT Products;

[Map_Product_CostPrice]:
MAPPING LOAD Product_ID, Cost_Price RESIDENT Products;

[Map_Product_Name]:
MAPPING LOAD Product_ID, Product_Name RESIDENT Products;

[Map_Product_Category]:
MAPPING LOAD Product_ID, Category RESIDENT Products;

[Map_Customer_Type]:
MAPPING LOAD Customer_ID, Customer_Type RESIDENT Customers;

[Map_Customer_Region]:
MAPPING LOAD Customer_ID, Region RESIDENT Customers;

Task 2.1 Solution Hints

Sales Amount Calculations:

Quantity * APPLYMAP('Map_Product_RetailPrice', Product_ID, 0) as Gross_Amount,
Gross_Amount * (Discount_Percent / 100) as Discount_Amount,
Gross_Amount - Discount_Amount as Net_Amount,
Quantity * APPLYMAP('Map_Product_CostPrice', Product_ID, 0) as Cost_Amount,
Net_Amount - Cost_Amount as Profit_Amount,
(Profit_Amount / Net_Amount) * 100 as Profit_Margin_Pct

Note: You may need to use preceding load pattern:

[Sales_Enriched]:
LOAD *,
    Gross_Amount - Discount_Amount as Net_Amount,
    Net_Amount - Cost_Amount as Profit_Amount
;
LOAD 
    *,
    Quantity * Retail_Price as Gross_Amount,
    Gross_Amount * (Discount_Percent / 100) as Discount_Amount,
    Quantity * Cost_Price as Cost_Amount
;
LOAD 
    Transaction_ID,
    // ... other fields
    APPLYMAP('Map_Product_RetailPrice', Product_ID, 0) as Retail_Price,
    APPLYMAP('Map_Product_CostPrice', Product_ID, 0) as Cost_Price
RESIDENT Sales_Raw;

Task 2.3 Solution Hints

Calendar Creation - Complete Solution:

[Calendar]:
LOAD 
    TempDate as Date,
    Year(TempDate) as Year,
    Month(TempDate) as Month,
    Dual(
        Text(Month(TempDate)) & ' - ' & Text(Year(TempDate)),
        Year(TempDate) & Num(Month(TempDate), '00')
    ) as Month_Name,
    'Q' & Ceil(Month(TempDate)/3) as Quarter,
    Year(TempDate) & '-' & Num(Month(TempDate), '00') as Year_Month,
    Year(TempDate) & '-Q' & Ceil(Month(TempDate)/3) as Year_Quarter,
    Week(TempDate) as Week_Number,
    WeekDay(TempDate) as Day_of_Week,
    If(WeekDay(TempDate) >= 5, 'Yes', 'No') as Is_Weekend,
    'Q' & Ceil((Month(AddMonths(TempDate, -3))/3)) as Fiscal_Quarter
;
LOAD 
    Date(MinDate + IterNo() - 1) as TempDate
WHILE MinDate + IterNo() - 1 <= MaxDate;
LOAD 
    Min(Transaction_Date) as MinDate,
    Max(Transaction_Date) as MaxDate
RESIDENT Sales_Enriched;

Task 3.2 Solution Hints

RFM Score Calculation:

[Customer_RFM]:
LOAD 
    Customer_ID,
    Max(Transaction_Date) as Last_Purchase_Date,
    Today() - Max(Transaction_Date) as Days_Since_Last_Purchase,
    If(Today() - Max(Transaction_Date) < 30, 5,
        If(Today() - Max(Transaction_Date) <= 60, 4,
            If(Today() - Max(Transaction_Date) <= 90, 3,
                If(Today() - Max(Transaction_Date) <= 180, 2, 1)
            )
        )
    ) as Recency_Score,
    Count(Transaction_ID) as Transaction_Count,
    If(Count(Transaction_ID) > 20, 5,
        If(Count(Transaction_ID) >= 16, 4,
            If(Count(Transaction_ID) >= 11, 3,
                If(Count(Transaction_ID) >= 6, 2, 1)
            )
        )
    ) as Frequency_Score,
    Sum(Net_Amount) as Total_Spending,
    If(Sum(Net_Amount) > 5000, 5,
        If(Sum(Net_Amount) > 2000, 4,
            If(Sum(Net_Amount) > 1000, 3,
                If(Sum(Net_Amount) > 500, 2, 1)
            )
        )
    ) as Monetary_Score
RESIDENT Sales_Enriched
GROUP BY Customer_ID;

// Add concatenated RFM score
LEFT JOIN (Customer_RFM)
LOAD 
    Customer_ID,
    Text(Recency_Score) & Text(Frequency_Score) & Text(Monetary_Score) as RFM_Score
RESIDENT Customer_RFM;

Expected Outputs

✅ Checklist of Deliverables

Script Tabs (Minimum 7 tabs):

• 01_Load_Products
• 02_Load_Customers
• 03_Load_Sales
• 04_Mappings
• 05_Sales_Enrichment
• 06_Calendar
• 07_Aggregations

Transformed QVD Files (6 files):

• Sales_Enriched.qvd
• Daily_Sales_Summary.qvd
• Product_Performance.qvd
• Customer_Summary.qvd
• Returns_Analysis.qvd
• Calendar.qvd

Dashboard Sheets (4 sheets):

• Sheet 1: Executive Overview (5 KPIs + 4 charts)
• Sheet 2: Product Analysis (4 charts + filters)
• Sheet 3: Customer Insights (5 charts + filters)
• Sheet 4: Returns &am ↑