Calculate sales for last N months

[#1]

I have a variable created to calculate the number of months back from which I need to calculate the sales.I also have sales listed by months.

For example, if n is 6, I need to calculate the sales from july 2014.

I tried several expressions but unable to seem to get it right. Can someone please help ?

[prabhuappu]

Hi,

try the below expression,

sum({<orderdate={'>=$(vMonth)'}>}Sales)

there are couple of things you need to follow when using date in set analysis...

keep the date in number format when you are storing the value in variable. Otherwise it will consider it as string.

please upload a sample application if you couldn't achieve it

Regards,

Prabhu Appu

[#3]

Hi Prabhu,

I tried your solution and did not work.I have a master calendar set up. The date is in yyyymm format. And the variable vMonth is an integer ( like 6 , if I need to calculate from six months ago)

[saurabh5]

if n is a variable/field which selects your current month, then you can have an expression as below:

sum({<n={">=$(=max(n)-6)<=$(=max(n))"}>}sales)

regards

Saurabh

[giakoum]

see attached rolling months example. courtesy of Gysbert Wassenaar

[#6]

Have you tried something along these lines?

=sum(if([DATEVALUE] > AddMonths(today(1),[VARIABLE]*-1),[SALESAMOUNT],0))

[#7]

And if you want to include data from the start of that month, see below.

=sum(if([DATEVALUE] > monthstart(AddMonths(today(1),[VARIABLE]*-1)),[SALESAMOUNT],0))

[prabhuappu · Accepted solution]

Hi Ravichandran,

Instead of storing month alone in a variable, store both YearMonth in it. You can use the below expression to Calculate YearMonth value of previous 6th month,

date(MonthStart(Date#('201205','YYYYMM'),-6),'YYYYMM')

Replace '201205' with your max(Date) value ($(=max(Date))). Since you have the date in YYYYMM format, max(Date) will return the current month or Max of month.

Use that variable in your set analysis as sum({<Date={'>=$(vMonth)'}>}Sales)

Please attach a sample, so that we can understand the problem that you are facing.

Regards,

Prabhu Appu

[#9]

Thank you for helping me. I managed to get it work.

[#10]

hi

I use the following expression, but there is a problem, when I select Month=2, the result only show which belong to Month 2, not the rolling result.

vBegin=Num(date(Addmonths(Date#(Max(MonthKey),'YYYYMM'),-11),'YYYYMMDD'),'#,##0')

vEnd=Num(Date(Max(DateDate),'YYYYMMDD'),'#,##0')

Count({<Month={$(vCurrentMonth)},Key={"<=$(vEnd)>=$(vBegin)"}>}Machine)