A "Simple" (not so Simple) Calculation in a "Totals" Row in a List

[Cognos_Jan2017]

A "Total" for entries in a Column Data Item of '< 1 Year' has [Total(< 1 Year)]

Example, the [Total(< 1 Year)] for 'AAA' is 4
and the [Total(< 1 Year)] for 'BBB' is 7

Would like to calculate a Percentage based on ...

Case
When [Level 1 Name]='AAA' Then (24-[Total(< 1 Year)])/24
When [Level 1 Name]='BBB' Then (56-[Total(< 1 Year)])/56
End

That would calculate for 'AAA' ...
(24-4)/24 = 20/24 = 83.33% (Data Format set to Percent, 2 decimal places)

Calculation for 'BBB' would be ...
(56-7)/56 = 49/56 = 83.5% (Data Format set to Percent, 2 decimal places)

Would like to place those results in a Column to the right of '< 1 Year' utilizing the Query Calculation.

Trying that w/ various Detail aggregation, and Summary aggregation is not working.

Gotta be a simple solution?  TIA, Bob

[Lynn]

A "Total" for entries in a Column Data Item of '< 1 Year' has [Total(< 1 Year)]

Example, the [Total(< 1 Year)] for 'AAA' is 4
and the [Total(< 1 Year)] for 'BBB' is 7

Would like to calculate a Percentage based on ...

Case
When [Level 1 Name]='AAA' Then (24-[Total(< 1 Year)])/24
When [Level 1 Name]='BBB' Then (56-[Total(< 1 Year)])/56
End

That would calculate for 'AAA' ...
(24-4)/24 = 20/24 = 83.33% (Data Format set to Percent, 2 decimal places)

Calculation for 'BBB' would be ...
(56-7)/56 = 49/56 = 83.5% (Data Format set to Percent, 2 decimal places)

Would like to place those results in a Column to the right of '< 1 Year' utilizing the Query Calculation.

Trying that w/ various Detail aggregation, and Summary aggregation is not working.

Gotta be a simple solution?  TIA, Bob

This must be a relational source, right?

I would avoid using the automatically generated total data items for further calculation. Just use the [< 1 Year] data item directly in your calculation instead of the [Total(< 1 Year)] data item. It should automatically aggregate to be the total for the list if you place it in the list footer.

[Cognos_Jan2017]

Thank you Lynn.

Yes, relational.

Will try this when get to work today, and post back results.

[Cognos_Jan2017]

Lynn ... Just tried, both in "Totals" Row, and a Footer below the "Totals" row ..

Case
When [Level 1 Name]='AAA' Then (22-[< 1 Year])
End

In this scenarioc, the Total for [< 1 Year] = 5
Cognos interprets (22-[< 1 Year]) as 110-5 = 105

Any known way to be sure the "22" is treated as only the value 22 ... similar to Visual Basic code?

TIA, Bob

[dougp]

This looks like an interesting problem.  Can you reproduce it from the GO Sales data and post the report spec?

[Cognos_Jan2017]

I don't have GO Sales data examples.

Still testing different things.

"Opportunity" to learn something here.

[Cognos_Jan2017]

Apparently, within a "Totals" row, "Insert Calculation" is NOT available?  IF it were (or we can workaround),
we might be able to solve this.

[Cognos_Jan2017]

Got it to WORK by ... PLUS the List Cell in the "Totals" row is a Calculated Summary.

Case
When [Level 1 Name]='AAA' Then (HowManyLocs - Total([< 1 Year]))/HowManyLocs
When [Level 1 Name]='BBB' Then (HowManyLocs - Total([< 1 Year]))/HowManyLocs
End

There is a 'CCC' [Level 1 Name}, but we want that List Cell to be Blank.

NOTE:  'HowManyLocs' is a Data Item of ...
Count([Location Code] For [Level 1 Name])