Cognos report to show count for quarters

[Avi]

Hi,

I have this requirement where I need to show the count of open tickets for the current and previous 3 quarters.
Basically, we have historical data from 2015 and there are open tickets for any year.
So here how my bar chart should look like

2020Q3 - open tickets from beginning till the end of 2020Q3
2020Q4 - open tickets from beginning till the end of 2020Q4
2021Q1 - open tickets from beginning till the end of 2021Q1
2021Q2 - open tickets from beginning till the end of 2021Q2

Any help is much appreciated.

Thanks

[BigChris]

You don't mention it in your description, but presumably you've got a field called something like [DateTicketOpened]

You also don't specify when your quarters run from and to, but I'll assume 1st Jan to 31st Mar etc.

So you could extract the year from the date, then calculate the quarter, based on a case statement that looks something like:

Code Select Expand

case
  when extract(month,[DateTicketOpened]) between 1 and 3 then 'Q1'
  when extract(month,[DateTicketOpened]) between 1 and 3 then 'Q2'
  when extract(month,[DateTicketOpened]) between 1 and 3 then 'Q3'
  when extract(month,[DateTicketOpened]) between 1 and 3 then 'Q4'
else '??'
end

After that it's just a matter of concatenating them together - you might need to cast the year.

[Avi]

Thanks for your quick response BigChris.

We have tickets being opening on daily basis and a few of them will be closed and a few are still in open status.
we have information like ticket number, ticket created date, status, and other details from the backend table.
Our quarters are like below
Q1 - Jan1 to Mar 31
Q2 - Apr1 to Jun 30 and so on

we have tickets from 2015 till date.
Now I want the count as below. Not just the tickets created in the last 4 quarters

2020Q3 - open tickets from 2015 till the end of 2020Q3
2020Q4 - open tickets from 2015 till the end of 2020Q4
2021Q1 - open tickets from 2015 till the end of 2021Q1
2021Q2 - open tickets from 2015 till the end of 2021Q2

For Example:
prior to 2020Q3   800   open tickets
      
           Open tickets for Quarter   Count I want to Show
2020Q3   300                                    800+300
2020Q4   200                                    800+300+200
2021Q1   100                                    800+300+200+100
2021Q2   200                                    800+300+200+100+200


Hope I am clear enough.

[BigChris]

Ok - that makes things clearer, but not necessarily easier.

I can't think of a clever way of doing this other than a calculation for each field:

2020Q1 =

Code Select Expand

extract(year,[DateTicketOpened]<2020 or (extract(year,[DateTicketOpened] = 2020 and extract(month,[dateTicketOpened]) between 1 and 3)

You might need to play around with sums or counts...

[Avi]

Hi

Thanks BigChris.

I achieved this by writing a query at FM.

SELECT YR,QTR,SUM(COUNT) OVER (ORDER BY YR,QTR) FROM (SELECT YR,QTR,COUNT(TICKET_ID) FROM TAB1 GROUP BY YR_NUM,QTR_NM)


Thanks