Author Topic: Can to calculate percentage?  (Read 224 times)

Offline posywang

  • Associate
  • **
  • Join Date: Aug 2017
  • Posts: 2
  • Forum Citizenship: +0/-0
Can to calculate percentage?
« on: 15 Aug 2017 08:26:15 am »
Hi,
I am new to Cognos Report Studio and would like to get some help on calculation below.

Resource   Month   CurrMth   Forecast Area   Current Area   Hours   Percentage
Amy   3   4   Finance   Finance   80   80%
Amy   3   4   Finance   Finance   20   20%
Amy   5   4   Finance   Finance   40   100%
Amy   5   4   Bond   Finance   60   100%
John   5   4   IT   IT   50   100%
John   7   4   IT   IT   50   100%


I realized the rule is a bit complicated than what I posted earlier so I modified the post.

If CurrMth is greater than Month, Current Area column will be used; otherwise, Forecast Area column will be used.

The part that I figured out is total([Hours] for [Resource],[Month],[Current Area])/total([Hours] for [Resource],[Month])

I tried to use if statement but failed. I was trying to use the following:

if([CurrMth]>[Month]) then (total([Hours] for [Resource],[Month],[Current Area])/total([Hours] for [Resource],[Month]) else
(total([Hours] for [Resource],[Month],[Forecast Area])/total([Hours] for [Resource],[Month])

How can this be done? Let me know if there's any additional question. Thanks!
« Last Edit: 15 Aug 2017 03:02:58 pm by posywang »

Online Lynn

  • Statesman
  • ******
  • Join Date: Apr 2008
  • Posts: 2,484
  • Forum Citizenship: +322/-1
Re: Can to calculate percentage?
« Reply #1 on: 15 Aug 2017 08:55:39 am »
Hi,
I am new to Cognos Report Studio and would like to get some help on calculation below.

Resource   Month   Hobby   Hours   Percentage
Amy   Jan   Fishing   80   80%
Amy   Jan   Movie   20   20%
John   Jan   Fishing   50   100%
John   Feb   Gardening   50   100%

I'd like to get percentage of time spent on each hobby per resource per month. For example, Amy spent 80% on Fishing in January, and 20% on movie. John spent 100% of his time on Fishing in Jan, and 100% on Gardening in Feb.

How can this be done? Let me know if there's any additional question. Thanks!

Are you reporting with a relational package or a dimensional package?

If it is relational take a look at the total function with a "for" clause:

Code: [Select]
[Hours] / total ( [Hours ] for [Resource], [Month] )

Offline posywang

  • Associate
  • **
  • Join Date: Aug 2017
  • Posts: 2
  • Forum Citizenship: +0/-0
Re: Can to calculate percentage?
« Reply #2 on: 15 Aug 2017 03:10:55 pm »
It works!!!! I create a new query using the same expression and it works. Not sure why but at least it works now.

 


       
Twittear