Author Topic: Adding two specific row's values in Crosstab (Analytics)  (Read 153 times)

Offline HelloCognos

  • Community Leader
  • *****
  • Join Date: Mar 2018
  • Posts: 100
  • Forum Citizenship: +0/-0
Ok, I have been struggling with this one for a while.  :) :)

I need add up two rows values and show it in a third row in a List or Crosstab.

Please see my mockup attached.

I tried using the new Calculation Data Item but it is not working.

Thanks again for your time.

Offline sdf

  • Statesman
  • ******
  • Join Date: Feb 2014
  • Posts: 456
  • Forum Citizenship: +3/-0
Re: Adding two specific row's values in Crosstab (Analytics)
« Reply #1 on: 09 Aug 2018 07:51:01 am »
have you tried to add a crosstab space?

Offline HelloCognos

  • Community Leader
  • *****
  • Join Date: Mar 2018
  • Posts: 100
  • Forum Citizenship: +0/-0
Re: Adding two specific row's values in Crosstab (Analytics)
« Reply #2 on: 09 Aug 2018 08:07:57 am »
Well, the issue I'm having is to how perform the calculation of the specific rows to be added together. Sure, Crosstab Space is a good idea but it seems to be more involved. Thanks a lot

Offline Lynn

  • Statesman
  • ******
  • Join Date: Apr 2008
  • Posts: 2,641
  • Forum Citizenship: +339/-1
Re: Adding two specific row's values in Crosstab (Analytics)
« Reply #3 on: 10 Aug 2018 02:20:29 am »
Ok, I have been struggling with this one for a while.  :) :)

I need add up two rows values and show it in a third row in a List or Crosstab.

Please see my mockup attached.

I tried using the new Calculation Data Item but it is not working.

Thanks again for your time.

Is your source relational or dimensional?

If it is dimensional, bring in the elements as individual members rather than a set of members. Then you can click the two desired members and create your calculation.

If it is relational, create a query calculation using a case statement to aggregate your metric for the two desired types.

Code: [Select]
case [Your Cost Type Field]
  when 'Maint Cost' then [Your Value Field]
  when 'Repair Cost' then [Your Value Field]
else 0
end

Offline HelloCognos

  • Community Leader
  • *****
  • Join Date: Mar 2018
  • Posts: 100
  • Forum Citizenship: +0/-0
Re: Adding two specific row's values in Crosstab (Analytics)
« Reply #4 on: 10 Aug 2018 06:17:53 am »
Thanks so much Lynn! I'll try this.

Offline HelloCognos

  • Community Leader
  • *****
  • Join Date: Mar 2018
  • Posts: 100
  • Forum Citizenship: +0/-0
RESOLVED: Adding two specific row's values in Crosstab (Analytics)
« Reply #5 on: 12 Aug 2018 07:45:22 am »
Lynn, :)

This approach worked perfectly for adding up couple of rows and showing them in a different row. Once the addition is showing, I excluded the others that were included in the addition from the Crosstab. Works like a charm.

Created the Measure based on the Case Statement. Assigned the Data Item as Summary Calculation: Calculated and drop it to the Crosstab.

Thank you again!

 


       
Twittear