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!
Quote from: posywang 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 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:
[Hours] / total ( [Hours ] for [Resource], [Month] )
It works!!!! I create a new query using the same expression and it works. Not sure why but at least it works now.