Cognos equivalent of excel's sumif() function

Fractional picture Fractional · Nov 6, 2014 · Viewed 7.9k times · Source

I'm familiar with Excel and SQL, but new to Cognos. I'm doing a conditional sum on [Total Margin] for each [Item Code]. This result should show on each row for the each item. I've tried 2 approaches in Cognos and a proof of concept in Excel. See below for sample data from a single [Item Code].

data screenshot

Total Item Margin A (Cognos)

case
when [free of charge flag] = 'FALSE'
then total([Total Margin] for [Item Code])
else null
end

The problem here is that the TOTAL result is incorrect, and just fails to display on the 2nd row.

Total Item Margin B (Cognos)

total([Total Margin] for [Item Code],[free of charge flag])

Here the TOTAL result is correct on most rows, but different on the 2nd row.

Total Item Margin C (Excel)

=SUMIFS([Total Margin],[Item Code],'10001430',[free of charge flag],FALSE)

So I can get the result I want using an excel SUMIFS formula. What Cognos query do I need to write to get the same result directly from Cognos?

Answer

Alexey Baturin picture Alexey Baturin · Nov 6, 2014

try

total(
  case
  when [free of charge flag] = 'FALSE'
  then [Total Margin]
  else null
  end
for [Item Code])