Split the results in to Financial Quarters

Hi,

I am working on a report which need to show the count based on year. Besides if the year is current year I need to split the results in to Financial quarters.

Sample date with the count is as follows:

ASSET_COUNT TARGET-DEPLOYMENT-DATE
5 5/31/12 12:00 AM
2 6/9/12 12:00 AM
2 5/17/12 12:00 AM
1 6/6/12 12:00 AM
1 5/25/12 12:00 AM

Please let me if nay details required further.

Thanks in advance
AR

Find more posts tagged with

Comments

mwilliams

You want to show the total of the count column for each quarter returned in the resultSet? If so, you should be able to use a crosstab to give you the total count per quarter fairly easily. Let me know if this doesn't help or if you need further assistance.

sairag

Hello Williams,

Thanks for the response. Actually I am developeing a chart. For which the x-axis will be the time intrval and y-axis will be count. Here when comes to time interval I am facing issue as I need to show the time in quarters for the current year and total time when it comes to prev year ...

Thanks
Sai

mwilliams

Sorry for the delayed response! Can you give a larger sample of data and what you'd like to see as the categories and bar value representations in your chart? Thanks!

sairag

Mr.Wiliiams here is the scenario.

I have 2 columns, one is the assetid (primary key), and the other is the deployment date of the specifc asset.
Here the deploye dae could be date from past (prev years), or current year or future date (next , or next ..year). My reqirement is to generate a report that shows the total count in a specifc year. While showing the count I need t present total count for the prev and future years, and for the current year it must be in quaters.

Let me know if I am missing any info required.

Thanks
AR

mwilliams

You might be able to create a computed column in your dataSet that outputs the year value if the year is not the current year and "2012 - Q1", etc. if the year is this year. Then, use this new column as your x-axis field. Let me know your version and I'll try to make you a sample.

sairag

Hello WIlliams - I am using the version 2.6.1. 
 
Let me know if I can provide further information. 
 
Thanks 
Sai 
 
<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="102077" data-time="1339187387" data-date="08 June 2012 - 01:29 PM">
You might be able to create a computed column in your dataSet that outputs the year value if the year is not the current year and "2012 - Q1", etc. if the year is this year. Then, use this new column as your x-axis field. Let me know your version and I'll try to make you a sample. </blockquote>

mwilliams

Take a look at this example. Look at the second computed column in the dataSet. The first computed column is just to make the sample db dates be more in line with today's dates. The second computed column checks the date fields year against this year. If it's this year, the month is checked to determine the value. If not, the year is output for the value. You then use this computed field as the x-axis value in your chart.