Hi,

my data looks like:

Account	Item	Balance
Alpha	Item A1	500
Alpha	Item A2	800
Alpha	Item A3	1200
Beta	Item B1	900
Beta	Item B2	1500
Gama	Item G1	100
Gama	Item G2	200
Gama	Item G3	200
Gama	Item G4	200

In Cognos (12.0.3) report crosstab, I need to categorize Accounts into:
- Top Accounts with total Balance > 1000 per Account
vs
- Long Tail with lower Balance.

I'm doing this by calculated field:

Category
if ([Balance] >= 1000)
then ('Top Account')
else ('Long Tail')

But I'm not able to instruct Cognos to summarize Items first, just then categorize the Accounts.
So the result is:

Account	Item	Balance	Category
Alpha	Item A1	500	Long Tail
Alpha	Item A2	800	Long Tail
Alpha	Item A3	1200	Top Account
Beta	Item B1	900	Long Tail
Beta	Item B2	1500	Top Account
Gama	Item G1	100	Long Tail
Gama	Item G2	200	Long Tail
Gama	Item G3	200	Long Tail
Gama	Item G4	200	Long Tail

But outcome I need is this:

Account	Item	Balance	Category
Alpha	Item A1	500	Top Account
Alpha	Item A2	800	Top Account
Alpha	Item A3	1200	Top Account
Beta	Item B1	900	Top Account
Beta	Item B2	1500	Top Account
Gama	Item G1	100	Long Tail
Gama	Item G2	200	Long Tail
Gama	Item G3	200	Long Tail
Gama	Item G4	200	Long Tail

How do I achieve it, please?
Thank you!

Quote from: MatusM on 04 Nov 2024 03:45:41 AMHi,

my data looks like:

Account	Item	Balance
Alpha	Item A1	500
Alpha	Item A2	800
Alpha	Item A3	1200
Beta	Item B1	900
Beta	Item B2	1500
Gama	Item G1	100
Gama	Item G2	200
Gama	Item G3	200
Gama	Item G4	200

In Cognos (12.0.3) report crosstab, I need to categorize Accounts into:
- Top Accounts with total Balance > 1000 per Account
vs
- Long Tail with lower Balance.

I'm doing this by calculated field:

Category

Code Select Expand

if ([Balance] >= 1000)
then ('Top Account')
else ('Long Tail')

But I'm not able to instruct Cognos to summarize Items first, just then categorize the Accounts.
So the result is:

Account	Item	Balance	Category
Alpha	Item A1	500	Long Tail
Alpha	Item A2	800	Long Tail
Alpha	Item A3	1200	Top Account
Beta	Item B1	900	Long Tail
Beta	Item B2	1500	Top Account
Gama	Item G1	100	Long Tail
Gama	Item G2	200	Long Tail
Gama	Item G3	200	Long Tail
Gama	Item G4	200	Long Tail

But outcome I need is this:

Account	Item	Balance	Category
Alpha	Item A1	500	Top Account
Alpha	Item A2	800	Top Account
Alpha	Item A3	1200	Top Account
Beta	Item B1	900	Top Account
Beta	Item B2	1500	Top Account
Gama	Item G1	100	Long Tail
Gama	Item G2	200	Long Tail
Gama	Item G3	200	Long Tail
Gama	Item G4	200	Long Tail

How do I achieve it, please?
Thank you!

Hi,

You can specify in the expression that you want to use the summary balance for each account, rather than the individual balance for each item. So your expression would be:

if (total([Balance] for [Account]) >= 1000)
then ('Top Account')
else ('Long Tail')

Cheers!

MF.

Thank you, works well!