Running total on top items

[MD]

Hi guys,

I'm new to Cognos & SQL, so I would need some help.

In a separate list I am trying to do the total on top 50, 100 & 200 items, so I've created a data item using the below expression, but I get the total for all items, not the tops.

CASE WHEN
(rank ([Net Sales])<51)
THEN
total([Net Sales])
ELSE
0
END

I have also tried using the function running-total (), but I do not know how to do it just for the top items.

Any suggestions are much appreciated.

[Lynn]

Hi guys,

I'm new to Cognos & SQL, so I would need some help.

In a separate list I am trying to do the total on top 50, 100 & 200 items, so I've created a data item using the below expression, but I get the total for all items, not the tops.

CASE WHEN
(rank ([Net Sales])<51)
THEN
total([Net Sales])
ELSE
0
END

I have also tried using the function running-total (), but I do not know how to do it just for the top items.

Any suggestions are much appreciated.

I suspect you need to include a "for" clause in your rank function. The case statement is working on a row at a time so of course the rank is 1 when only one row at a time is evaluated.

Try adding "for report" as the scope of the rank and see if that does what you want. It is also possible to use another query item as the scope if, for example, you wanted rank within some grouping.

Code Select Expand


rank ( [Net Sales] for report)


Hope this helps :)

[MD]

Hi Lynn,

Thanks for the reply.

What I am trying to do is sum up the total Net Sales for top 50 items by sales.

If I use rank ([Net Sales] for report) it will just rank all items in the report, right? But how do I get just the total for the top 50?

[Lynn]

Hi Lynn,

Thanks for the reply.

What I am trying to do is sum up the total Net Sales for top 50 items by sales.

If I use rank ([Net Sales] for report) it will just rank all items in the report, right? But how do I get just the total for the top 50?

The case statement approach you outlined originally would be the way to go. I've attached a sample from the great outdoors model to illustrate just the top 3 product lines.

[MD]

Sorry, I'm using Cognos 10 and it seems the xml is not compatible  :(

[Lynn]

Sorry, I'm using Cognos 10 and it seems the xml is not compatible  :(

I don't have a version 10 environment anymore. You can try to edit the first line of the XML file to set the schemas and style version to match whatever yours should be. Just get the xml for your report and try replacing those.

The expression for top 3 as an illustration is this:

total (
case
  when rank ( total ( [Revenue] for [Product line] ) ) <= 3 then [Revenue]
else 0
end
for report )

[MD]

I don't have a version 10 environment anymore. You can try to edit the first line of the XML file to set the schemas and style version to match whatever yours should be. Just get the xml for your report and try replacing those.

The expression for top 3 as an illustration is this:

total (
case
  when rank ( total ( [Revenue] for [Product line] ) ) <= 3 then [Revenue]
else 0
end
for report )