cognossumifscognos-10cognos-8

Cognos equivalent of Excel Sumif() function without a static variable


Here is the following example that I found that does work as a sumif function.

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

My question is how to replace the 'FALSE' portion and have the expression reflect the [free of charge flag] for that specific row. So as the report builds the total in each row may switch from a total of that item code and the free of charge flag being false to true depending on what is in the specific row.

This fails to run and I believe this may not be possible.


Solution

  • You have [Item Code] as the column you are rolling up to but you can roll up to multiple columns.

    Try this:

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

    This will total the measure across all combinations of [Item Code] and [free of charge flag]. For a given [Item code] 'TRUE' and 'FALSE' rows will have their own values.

    Further, if you don't want to show a total when the value is 'TRUE' then you can wrap this in a CASE statement:

    CASE [free of charge flag]
    WHEN 'FALSE' THEN total([Total Margin] for [Item Code],[free of charge flag]) 
    ELSE NULL
    END
    

    All rows with 'FALSE' will show the total for that particular row's [Item Code] and all others will have NULL.