topic 12 rolling month in QlikView

12 rolling month

Wed, 09 Nov 2011 19:30:56 GMT

Hello All,

I have a problem with my expression, I have a field in my table name DATE which is like

2011-12-01

2011-11-01

2011-10-01

. I want to filter the report for last 12 month or better to say I need the 12 rolling month in the report.

Any idea is appreciated.

Thanks

Nima

12 rolling month

Wed, 09 Nov 2011 19:44:06 GMT

This Year Rolling Sales Amount : sum( {$< NumOrderDate = { '>= $(LastYearvarDate) <= $(varDate)'}>} NetSalesAmount )

Tha variables are

varDate = variable choosen in a calendar Objet

Last YearvarDate = num(AddMonths(date($(varDate)) , -12 ))

Hope that helps!

Philippe

12 rolling month

Wed, 09 Nov 2011 19:51:32 GMT

Thanks Philippe,

But I need to show the last 12 months in the report that I have.

I don't think if it works for me.

Thanks,

Nima

12 rolling month

Wed, 09 Nov 2011 19:56:54 GMT

And if you put the Month(Date) as a Dimension ?

It should show you per month , the amount of the last 12 months no ?

12 rolling month

Wed, 09 Nov 2011 20:12:17 GMT

Ok ,

This the case, maybe I did not explain it very well.

What I want is when I open the report the report shows me the last 12 month of the DATE that I have.

I put this expression in a trigger but it does not work:

=('>' & date(addmonths(Today(),-12), 'YYYY-MM-01') AND
'<' & date(Today(), 'YYYY-MM-01') )

Thanks

12 rolling month

Wed, 09 Nov 2011 20:16:39 GMT

what kind of object are you using ?

A chart ? A listBox ? something Else

Try

if (DATE <= Today(0) and DATE >=Addmonths(Today(0) , -12 ) , DATE , null())

Then Remove the null .

12 rolling month

Wed, 09 Nov 2011 20:30:22 GMT

Thanks,

The thing is I need it on the trigger not in the object because I don't want to lose the rest of my DATE .

When it's on the trigger , when I open the report it shows just the 12 month then I have this option to clear the selection and select other month in the month table.

Thanks

12 rolling month

Wed, 09 Nov 2011 20:31:44 GMT

Hi Nima,

For my rolling 12 month report used the following formula under an expression

the report is for the last completed 12 months sales for the company's branches

=if(InvoiceDateID <= MonthEnd(Today(),-1) and InvoiceDateID > MonthEnd(Today(),-13) , BRANCHES,Null())

so if i were to do it for the last rolling 365 days I use

=if(InvoiceDateID <= Today()-1 and InvoiceDateID >= Today()-366 , BRANCHES ,Null())

hope this helps

Tara

12 rolling month

Wed, 09 Nov 2011 20:41:21 GMT

Thanks ,

Your answer is helpful but not exactly I'm looking for.

Cheers,

Nima

12 rolling month

flipside — Wed, 09 Nov 2011 21:26:54 GMT

Whenever I am faced with rolling date periods, I usually create a new field within the load script to hold a flag value.

For example, if Date within last 12months, set YTDflag to 1, else set YTDflag to 0. It makes expressions a lot easier to manage, in your case you just need to make the selection of YTDflag = 1.

Hope this helps

flipside

Re: 12 rolling month

SunilChauhan — Thu, 10 Nov 2011 05:29:50 GMT

please see the attached ,

hope this help

Re: 12 rolling month

neetu_singh — Thu, 10 Nov 2011 07:22:22 GMT

Hi Nima,

you can try this for current 12 rolling month:

=RangeSum(Below(sum({$<Year=,Month=,[Cal Year]=,[Year Month]=,OrderDate={"<=$(vMaxDate) >=$(vRollingDate)"}>}

NetSales),0,12))

Variable values are:

1) vMaxDate = (Max(OrderDate))

2) vRollingDate = AddMonths(Max(OrderDate),-11)

Hope it will help you.

Thanks & Regards

Neetu Singh