Rolling 2 months

[#1]

Hi ,

I need a expression for rolling 2 months. I have two fields month and amount. I need to sum rolling two months from the month selected. Below is the example of my need

Ex:

Month Amount

Jan 08 10

Feb 08 20

Mar 08 30

Apr 08 40

May 08 50

If one selects a month May, the sum should be for rolling two months from the selected month, i.e the result should be addition of May and Apr amount (Ans:90) and if one selects a month Mar, the sum should be for Mar and Feb amount (Ans:50).

I need this to be used in the expression of a chart. If someone can explain with an example application, it would be greatful.

Thanks

[#2 · Accepted solution]

Hi,

You need to use InMonthToDate ( date, basedate , shift ) function to achieve your requirement.

In my attached application you can see, if nothing is selected , It will take the max of the date and sum up with the previous month, if u select Apr 2009, it will sum up Apr 2009 and Mar 2009.

Expression would be as follow.

=sum(all<Month> if(
InMonthToDate(Month,Vmax,-1) or InMonthToDate(Month,Vmax,0),Amount))

Here variable Vmax is used to store the max of the date field. for further detail please have a look at the attached application.

Gud luck

-Sridhar

[#3]

Thanks sridhar, this is what I exactly want.

Thanks.

[#4]

Hi Sridhar,

I am facing a new problem when I included Country in the table. It doubles every time.

When I select the country 'LA', the result should be 90, but it is displaying 180 and so on. Look at the attachment

Can you please help me on this.

Thanks.

[#5]

Hi Sridhar,

Can you please answer my question in the thread below

http://community.qlik.com/forums/p/16410/63981.aspx#63981

Thanks

[#6]

Hi,

Sry I`m bit late. check the attached application now.

- Sridhar

[#7]

Hi Sridhar,

Thanks for your constant help.

But still I have the problem. The formula is not working for multiple countries selected and is it possible to remove the distinct fuction and the make this work.

Thanks.