Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

22 Replies
felipedl
Partner - Specialist III
Partner - Specialist III

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.

Anonymous
Not applicable
Author

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)

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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

mostwanted123
Creator
Creator

Hi Nagamani,

Try this expression,

Sum({<DateField={">=$(=addmonths(max(DateField),-11))<=$(=max(DateField))"},Year=,Month=,Quarter=>}Sales)

Regards,

Pratik Bhor

Anonymous
Not applicable
Author

hi

can you provide QVF for this

balar025
Creator III
Creator III

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