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]
.
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?
try
total(
case
when [free of charge flag] = 'FALSE'
then [Total Margin]
else null
end
for [Item Code])