WEBI - Cross Tab - rolling 12 months

[Former Member]

= - datatype numeric

= - datatype numeric

In my webi report I want to fetch profit for for rolling 12 months show in cross tab for each year/month.

How do I fetch rolling 12 months

[former_member198519 · Accepted solution]

You have couple of options! What you can do is create a filter at the universe level with the below code, which will serve the purpose:

"Database date field" > add_months(trunc(sysdate),-12)

Or

at the webi report create a filter variable:

[filter]=if([Date]>Relativedate(Currentdate();-365)) then "Show" else hide

Set the variable to "Show"

P.S: Creating the filter at universe level is a best practice.

[Former Member · Accepted solution]

I have these two fields in universe so how will I write universe filter to get only rolling 12 months of data

[Report Year] = [2016,2015,2014,2013] - datatype Numeric

[Report Month] = [1,2,3,4,5,6,7,8,9,10,11,12] - datatype Numeric

[Profit]

[Product]

I dont have a date field but separate [Report Year] & Month

[former_member198519 · Accepted solution]

Can you give me some sample data to work on?

In the meantime can you try this:

=if([Month]>(MonthNumberOfYear(RelativeDate(CurrentDate();-365)-1)) and [Year]>Year(RelativeDate(CurrentDate();-365))) then "Show" else "Hide"

[Former Member · Accepted solution]

This will be filter on what sales?

Report Year	Report Month	Product	Sales
2015	1	ABC	123
2015	2	XYZ	345
2015	3	ABC	567
2015	4	CVB	789
2015	5	DFG	1011
2015	6	DFG	1233
2015	7	RTY	1455	Rolling 12 months start
2015	8	UUI	1677
2015	9	oiu	1899
2015	10	kjh	2121
2015	11	lkj	2343
2015	12	mnb	2565
2016	1	mnb	2787
2016	2	poi	3009
2016	3	qwe	3231
2016	4	cft	3453
2016	5	vgy	3675
2016	6	bhu	3897
2016	7	nji	4119	Rolling 12 months end

[Former Member · Accepted solution]

o/p	2016								2015
Products	7	6	5	4	3	2	1		12	11	10	9	8	7
ABC	Sales
CVB

[Former Member · Accepted solution]

Kuldeep, any luck?

Where do I put the below formula? in Conditional formatting?

=if([Month]>(MonthNumberOfYear(RelativeDate(CurrentDate();-365)-1)) and [Year]>Year(RelativeDate(CurrentDate();-365))) then "Show" else "Hide"

I also have pasted sample data

[former_member198519 · Accepted solution]

Created two filter variable:

[Month]=[Report Month]>(MonthNumberOfYear(RelativeDate(CurrentDate();-365)))

[Year]= [Report Year]>Year(RelativeDate(CurrentDate();-365))

And condition would be Month=1 or year=1

Here is what i get

[Former Member · Accepted solution]

This works perfect, just one more thing. Can I do 13 months instead of 12 months? Not a hard requirement but the end user asked me if its possible.

[Former Member · Accepted solution]

Also can I create some kind of universe filter like 'rolling 12 months' in UNV so that I dont have to do this report filter in each report

[former_member198519 · Accepted solution]

if you have a date object then it would be very easy to create a filter for rolling 12 month, in your scenario the best way would be to use it like a filter. As for your other query, just change the 365 number to the number of days you want to go back.

[Former Member · Accepted solution]

rather than 365 days when the some months have 30/31/28 days wouldn't the 13 month calculation makes sense to be done more on some how by 'months'?

[former_member198519 · Accepted solution]

365 is the exact number of days in a year, that is the reason we are calling it a rolling 12 month period. If you need to add the 13th month as well, then you need to have a logic in place to check weather the month is of 30, 31 or 28 (in case of Feb)