Straight Table with Sales of Selected Year and Previous Year Columns

by

Summary

The user is struggling to create a Straight Table in Qlik with three columns: current month sales, previous year sales for the same month, and a formatted date. The user attempted to use complex Set Analysis modifications to manually shift the selection context, which resulted in zero values or errors. The core issue is that the user is trying to manually manage the temporal context using specific hard-coded field manipulations (e.g., YYYYMM = {"$(=Only(YYYYMM)-100)"}), which fails because it does not account for the actual calendar hierarchy and the behavior of Only() within a table dimension.

Root Cause

The root cause is the misuse of Set Analysis for a temporal comparison that should be handled by an Aggr() function or a specific dimension approach. The expressions failed for two primary reasons:

  1. Invalid Set Modification Logic: The user attempted to manipulate the YYYYMM field using $(=Only(YYYYMM)-100). This calculates a value based on the current row’s context. In a Set Analysis modification ({<...>}), this modified set is applied before the table loops through the dimensions. Therefore, it cannot reliably “shift” the context of the current row to the previous year while keeping the dimension label aligned.
  2. Missing Reference Fields: The user tried to use date shifting functions like AddYears() on FirstDateofMonth and LastDateofMonth without establishing a correct link between the sales data and the calendar in the expression logic, leading to empty results.

Why This Happens in Real Systems

In real-world Qlik deployments, this confusion stems from the difference between Direct Selection and Set Analysis.

  • Qlik’s Associative Engine: By default, Qlik links the SalesDate to the MasterDateCalendar automatically. When a user selects a month, the engine filters both tables instantly.
  • The “Power BI” Mentality: Users coming from SQL-based tools (like Power BI) expect to “program” the logic (e.g., “fetch this value if date = last year”). They attempt to force Qlik to behave procedurally rather than associatively.
  • Set Analysis Misconception: Users often view Set Analysis as a way to “rewrite the query” for specific rows. In reality, Set Analysis calculates an expression against a fixed data set outside the standard selection state, which is why Only() inside a Set Analysis modifier often fails—it resolves to a single value before the table iterates.

Real-World Impact

  • Performance Degradation: Complex nested Aggr() functions or heavy Set Analysis modifiers on large datasets cause significant calculation lag in Straight Tables, making dashboards unresponsive.
  • Data Inaccuracy: Hard-coded logic (like subtracting 100 from a YYYYMM integer) fails at year boundaries (e.g., December 2023 minus 100 is not January 2023; it is 202203), leading to incorrect data being presented to stakeholders.
  • Maintenance Debt: Code that relies on hard-coded calendar fields is brittle. If the data model changes or calendar granularity differs, the dashboard breaks.

Example or Code

Below is the standard Qlik approach to solve this. The key is using an Aggr() function to create an iterative calculation that shifts the year while maintaining the current row’s context.

// 1. Dimension: [MMM YYYY]
// Create a master calendar field combining Month and Year for display
[MMM YYYY] = Date([SalesDate], 'MMM YYYY')

// 2. Expression for Current Sales (Column 2)
=Sum([SalesAmount])

// 3. Expression for Previous Year Sales (Column 3)
// This uses Aggr() to iterate over the current rows and calculate the 
// sum for the specific shifted date.
=Sum(
    Aggr(
        Sum({} [SalesAmount]),
        [MMM YYYY]
    )
)

Note: The expression above assumes a standard Date/Year hierarchy. For a more robust solution that handles specific calendar requirements, creating a flag in the data load script is preferred.

How Senior Engineers Fix It

Senior engineers avoid modifying Set Analysis for temporal comparisons in Straight Tables. They use one of two methods:

  1. The Aggr() Method (Frontend):

    • Technique: Use Aggr(Sum(Year=Max(Year)-1), Dimension). This forces Qlik to calculate the sum for the previous year for every unique value of the current dimension.
    • Benefit: It creates a “virtual” associative link for the calculation without breaking the row context.

  2. The Synthetic Dimension / Calendar Flag (Backend):

    • Technique: In the Data Load Editor, create a flag in the MasterDateCalendar table. 
      LOAD
Date,
Month,
Year,
YYYYMM,
// Create a unique identifier for the month/year combination
'LY' & Year-1 & '-' & Month as [LY_Key],
// Flag for previous year sales
Sum(SalesAmount) as [Sales LY] // Usually requires a join or applymap
RESIDENT SalesTable;
    • Benefit: This is the most performant method. It moves the heavy lifting to the load script, allowing the chart to simply Sum([Sales LY]) without complex runtime calculations.

Why Juniors Miss It

  • Over-reliance on Set Analysis: Juniors often learn Set Analysis early and try to apply it to every problem, not realizing that Aggr() is often the better tool for row-by-row context shifting.
  • Lack of Data Model Understanding: They often fail to realize that Only(Date) returns a value, not a filter context. They try to mathematically manipulate that value (e.g., Only(Date) - 365) expecting Qlik to translate it back into a filter, which it does not.
  • Trying to Replicate SQL Logic: They attempt to write procedural logic (IF/THEN) rather than associative logic. They look for a function like SAMEPERIODLASTYEAR() because that is the SQL/Power BI way of asking the database; in Qlik, you ask the data model via associations.