Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

manigunta
Contributor

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
Valued Contributor III

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.

psilva001
Contributor

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)

rajankasbe91
New Contributor III

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

manigunta
Contributor

Re: rolling 12 months

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
Contributor

Re: rolling 12 months

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
New Contributor III

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'))

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
Contributor

Re: rolling 12 months

Hi Nagamani,

Try this expression,

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

Regards,

Pratik Bhor

manigunta
Contributor

Re: rolling 12 months

hi

can you provide QVF for this

balar025
Contributor III

Re: rolling 12 months

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

Community Browser