基于IBM Cognos Analytics 11.0.9的活跃记录展示及季度末统计技术问询

Hey there! Let’s tackle your two questions step by step—since you’re new to Cognos, I’ll keep things focused on practical, testable actions rather than abstract concepts.

1. Display Active Records Over a Time Dimension #

First, let’s clarify: an "active record" here means any record where the current time dimension member (day, month, quarter, etc.) falls between its open date and close date (including the open date, and we’ll handle null close dates as "still active").

Here’s how to set this up:

Option A: Daily Active Records #

If you want to show which records were active on each individual day:

• Drag your time dimension (with date hierarchy) into the report’s row/column area, set it to "Day" level.
• Create a calculated measure to flag active records:

  CASE
    WHEN [开单日期] <= [Time Dimension Date] 
         AND ([关单日期] >= [Time Dimension Date] OR [关单日期] IS NULL)
    THEN 1
    ELSE 0
  END
  

• Drag this calculated measure into the metric area, and change its aggregation to Sum (or use COUNT on a unique record ID where the case returns 1) to get the total active records per day.

Option B: Active Records for Longer Time Periods (Month/Quarter/Year) #

If you want to count records that were active at any point during a month/quarter/year:

• Use Cognos’s Range Association in your data module:
  1. Go to your data module, select the time dimension table and your fact table (with open/close dates).
  2. Create a new association where the time dimension’s date falls between [开单日期] and [关单日期] (include null close dates as "active indefinitely").
  3. Back in your report, drag the time dimension (set to month/quarter/year) and a count of unique record IDs into the report—Cognos will automatically count all records that overlapped with that time period.

2. Count Active Records on the Last Day of Each Quarter #

This builds on the first question, but we’ll narrow it to only the final day of each quarter.

• Step 1: Identify Quarter-End Dates
  Cognos has a built-in function to get the last day of a quarter—use it to target those dates:

  _last_of_quarter([Time Dimension Date])
  

  If your time dimension doesn’t have a pre-built "Quarter End Date" attribute, create a calculated field with this function to generate it.

• Step 2: Calculate Active Records on Quarter-End
  Create a calculated measure that counts records active on that quarter’s last day:

  COUNT(
    CASE
      WHEN [开单日期] <= _last_of_quarter([Time Dimension Date]) 
           AND ([关单日期] >= _last_of_quarter([Time Dimension Date]) OR [关单日期] IS NULL)
      THEN [记录ID] -- Replace with your unique record identifier
      ELSE NULL
    END
  )
  

• Step 3: Group by Quarter
  Drag your time dimension’s "Quarter" level into the report, and add the calculated measure. This will show you the total active records on the final day of each quarter.

Quick Tips for New Users #

• Always test with a small dataset first to verify your calculations work as expected.
• Don’t forget to handle null close dates—these represent records that are still active, so they should be included in your active counts.
• If you’re working in a report (not a data module), you can create calculated fields directly in the "Data" tab under "Calculations".

内容的提问来源于stack exchange，提问作者Olga Hauze