Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Support Case Portal will move to Qlik Community Oct 4. Temporary case outage Oct 3. READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
manigunta
Creator
Creator

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
Partner

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.

psilva001
Creator
Creator

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)

rajankasbe91
Contributor III
Contributor III

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

manigunta
Creator
Creator
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

manigunta
Creator
Creator
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

kathleene
Contributor III
Contributor III

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

manigunta
Creator
Creator
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.