22 Replies Latest reply: Oct 10, 2017 1:58 AM by sahadev patil

# rolling 12 months

HI

I have date field with 3 years dates. But i need to get 12 months rolling.

How can we get 12 months rolling

can some one suggest on this

• ###### Re: rolling 12 months

Hi nagamani,

Don't quite understand what you want?

You have a date field that has only year (like 2017,2016,2015) and want to transform into months (jan-2017,feb-2017,...) ?

Felipe.

• ###### Re: rolling 12 months

I don't understand too.

But, maybe you want show in your chart just 12 months...

If it's right, you must select the "Restrict wich values are displayed using the first expression" in "Dimension Limit",  and specify your limit number(12)

• ###### Re: rolling 12 months

HI

I have a column which is in 'YYYYMMDD' format. This column has dates from 2013 to till date.

I need to display last 12 months at any point of time in 'MMM YY' format.

I converted the date to 'MMM YY' required format.Now i have the change to display rolling 12 months.

Can you suggest how can I get last 12 months rolling data all the time

thanks

• ###### Re: rolling 12 months

Hi Paulo,

Hope this helps you.

<PeriodID = {"<=\$(=Max(PeriodID)-12)"}, Year = {"\$(=Max(Year) -

1)"}, Quarter = , Month = , Period = >

I have used this expression in one of my projects:

sum({<[Opportunity Close Date] = {">=\$(=MonthStart(Max([Opportunity Close Date]),-2))<=\$(=MonthEnd(Max([Opportunity Close Date])))"},

[Opportunity Won/Lost]={'WON'},[Opportunity Closed_Flag]={'1'},Year=,Month=>}[Opportunity Amount])

-2 is the number the months i want to roll in chart.

Regards,

Rajan

• ###### Re: rolling 12 months

HI

I have a column which is in 'YYYYMMDD' format. This column has dates from 2013 to till date.

I need to display last 12 months at any point of time in 'MMM YY' format.

I converted the date to 'MMM YY' required format.Now i have the change to display rolling 12 months.

Can you suggest how can I get last 12 months rolling data all the time

thanks

• ###### Re: rolling 12 months

Hello Nagamani

I have a chart which shows the last 12 months based on the date filter.  For example if the user filters to March of 2017 the chart will show April of 2016 – March of 2017.  This works by using variables:

vMaxSelMonth = num(date(max(MonthYear),'M/D/YYYY'))

The dimension is based on MonthYear and each expression is based on the variables

=sum({<[Month Start]={'<=\$(vMaxSelMonth)>=\$(vRolling12Months)'},MonthYear=,[Month Name]=,[Year]=,Quarter=>}[FieldtoSum])

Hope this helps.

Kathleen

• ###### Re: rolling 12 months

hi

can you provide QVF for this

• ###### Re: rolling 12 months

Hi Nagamani,

Try this expression,

Regards,

Pratik Bhor

• ###### Re: rolling 12 months

hi

i need rolling 12 months, For example if the user filters to March of 2017 the chart will show April of 2016 – March of 2017.

Based on your expression, i tried below but getting as invalid dimension

• ###### Re: rolling 12 months

first make date field to month year

date(date#(datefield,'YYYYMMDD'),'MM-YYYY') as MONTH_YEAR

and use below expression

• ###### Re: rolling 12 months

HI

thanks for reply, this is working.

if i want to get 12 months rolling always comparing with current date , how We have to use current date here.

• ###### Re: rolling 12 months

use date(today(),'date format') for current date

• ###### Re: rolling 12 months

hi,

i am using below expression , but i am geting blank data, where i suppose to get 12 records with last 12 months.

• ###### Re: rolling 12 months

HI

Can you please give provide qvw, where it shows 12 months rolling data, please dont add measures here as usm or max.

• ###### Re: rolling 12 months

HI

i am able to get have created two varaibles var1 , where i am able to get value as month -year and var2 getting last 12 months as month- year

But when i trying to do var2 >= var1 to get difference as 12 months in one column  it is just  giving data as -1 ( whcih shos as correct) and it is not giving 12 months.

var1:date(date#(datecolumn,'YYYYMMDD'),'MM-YYYY')

I need all var2>=var1 values

• ###### Re: rolling 12 months

Check out attached file for reference. It has all rolling and YTD functions.

• ###### Re: rolling 12 months

Hi

Using  Calendar.qvw, I have used rolling type: R3M and period:22-05-2014 then I@ am able to see May, Jun, July in the 2014 year.

but my requirement is if I give period:22-05-2014 then I need to get  March, April, May in 2014.

How can I get that?

• ###### Re: rolling 12 months

try this its work fine

sum({<Date = {">=\$(=num(MonthStart(Max(Date),-11)))<=\$(=num(MonthEnd(Max(Date))))"}

,YEAR=,MONTH=>}[Opportunity Amount])

Regards

• ###### Re: rolling 12 months

HI

Can you please give provide qvw, where it shows 12 months rolling data, please dont add measures here as usm or max.

• ###### Re: rolling 12 months

Hello Friend

Sum({<Date={">=\$(=Monthstart(max(Date)-11))<=\$(=max(Date))"},Year=,Month=>} Sales)

Regards

Vikas

• ###### Re: rolling 12 months

Hi

Giving blank, can you qvw file for this

• ###### Re: rolling 12 months

Try this its work fine .

I have removed only \$ symbol before the Field [Month-Year]