0

I am new in using Cognos and I have data for the overall project, and I need to create some kind of table or cross tab may be to count the overall project of each year and how many of them are active, canceled and inactive

I have tried using a cross tab but no success. 

ProjectId  Status          Date
 1589   Active          8/29/2018
 1566   Inactive    4/17/2018
 1042   Cancelled   1/6/2014
 1374   Completed   1/20/2015
 1543   Completed   8/4/2014
 1065   Cancelled   7/15/2014
 1397   Completed   10/1/2012
 1520   Inactive    4/13/2017
 1420   Completed   1/1/2015
 1443   Completed   1/1/2015
 1048   Cancelled   10/16/2014
 1002   Active          2/6/2017
 1357   Completed   1/19/2017
 1606   Active          11/6/2018

Output should look like this 

New Projects    Active      Cancelled/Terminated/Inactive   Carried Forward  
2013             32            45          4         11         30  
2014             45            75          17        14         44  
2015             46            90          25        21         44  
2016             30            74          27        10         37  
2017             82            119         11        26         82  
2018             86            168         29        24         115 
2019             23            138         9         4          125
Siong Thye Goh
  • 3,518
  • 10
  • 23
  • 31
WonderWoman
  • 33
  • 4

1 Answers1

0

Going with -- project Id, status, Date

The ideal scenario is we have a data item for year. If not, to get the year

  extract(year, Date)

Calculation data items: for each count

For example, this is for active

if (status = 'Active')Then(1)Else(0)

For properties Make sure the aggregation is set to total

Adding the column should give you the count

VAI Jason
  • 534
  • 4
  • 14