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
Quote from: Cognos_Jan2017 on 26 Sep 2017 01:46:21 PM
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.
Thank you Lynn.
Yes, relational.
Will try this when get to work today, and post back results.
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
This looks like an interesting problem. Can you reproduce it from the GO Sales data and post the report spec?
I don't have GO Sales data examples.
Still testing different things.
"Opportunity" to learn something here.
Apparently, within a "Totals" row, "Insert Calculation" is NOT available? IF it were (or we can workaround),
we might be able to solve this.
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])