How to display last 6 quarter data?

[Former Member]

Hi Team,

Right now, my report pulls in the data for current year and previous year as shown below. I would require my report to display only the last 6 quarter at any point of time. For example, If the current month is Jan 2016. Then my report should display, Q1 of 2016, Q4 2015, Q3 2015, Q2 2015, Q1 2015 and also Q4 2014 which would be the last 6 quarters. Could you please help me achieve this?

Thanks,

Sid

[Former Member]

Hi Siddharth,

Try creating a filter condition at the universe level as below

table.date between

trunc(sysdate,'q')-1 and add_months(trunc(sysdate,'q'),-18)

If you have other date objects like month / year to filter the data let us know so that we can filter at month level data rather than day level which will be faster. For that try below filter

table.year*100+table.month between

extract(year from trunc(sysdate,'q')-1)*100+

extract(month from trunc(sysdate,'q')-1)

and

extract(year from add_months(trunc(sysdate,'q'),-18))*100+

extract(month from add_months(trunc(sysdate,'q'),-18))

These functions works for oracle only. you need to modify the syntax based on your database

[Former Member]

Hi Divya,

Thanks for the message, will check and write back to you.

Regards,

Sid

[mhmohammed]

Hi Siddharth,

I assume you want to pull data for last 6 Quarters from the database, and for that you need a dynamic filter which can do that. Is that correct? If yes, we'll to need to know what is the backend database you're working with, to create a filter that gets data for last 6 Quarters, as functions and syntax differs by database.

Example: For Oracle, filter to get last 6 Quarters would be like:

<Table.DateColumn>      between      trunc(add_months(sysdate,-18),'MM')      and      sysdate

You'll have to replace <Table.DateColumn> in the above formula with the Date object you have and, how the formula works is, it'll bring data between dates 18 months prior to current date and current date.

Let us know if you have any questions.

Thanks,

Mahboob Mohammed

[Former Member]

Hi Mahboob,

Thanks for the message, will check for the database.

Regards,

Sid

[amitrathi239]

Sort your crosstab by Descending on Year.

Create one Variable RunningCount=RunningCount([Quarter])

Add the ranking on Runningcount variable.

[Former Member]

Hi Amit,

Thanks for your reply.

It doesn't work well in cross tab like the one below,

Before applying rank:

After applying rank:

Though I gave top 3, I could just see top 1 and also I could see only 4 rows of data instead of the complete one. I applied rank on runningcount only and followed the same procedure.

Could you please give a try on cross tab and let me know.

Thanks in advance!

Regards,

Sid

[Former Member]

Hi Amit,

Change RunningCount formula by: =RunningCount([Quarter]; col)

Didier