Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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)
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
HI
thanks for your reply
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
HI
thanks for your reply
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
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'))
vRolling12Months = num(AddMonths((vMaxSelMonth),-11))
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
Hi Nagamani,
Try this expression,
Sum({<DateField={">=$(=addmonths(max(DateField),-11))<=$(=max(DateField))"},Year=,Month=,Quarter=>}Sales)
Regards,
Pratik Bhor
hi
can you provide QVF for this
Check out attached file for reference. It has all rolling and YTD functions.