Sales Last 12 Months

[#1]

Hi,

I have a formula in Pivotable, which is correctly however it need to much time for the result.

Is there any other formula to same result with few time

my formula is:

sum(all<[Calender Year Month]> if(
InMonthToDate([Calender Year Month],Vmax,0) or
InMonthToDate([Calender Year Month],Vmax,-1) or
InMonthToDate([Calender Year Month],Vmax,-2) or
InMonthToDate([Calender Year Month],Vmax,-3)or
InMonthToDate([Calender Year Month],Vmax,-4)or
InMonthToDate([Calender Year Month],Vmax,-5) or
InMonthToDate([Calender Year Month],Vmax,-6)or
InMonthToDate([Calender Year Month],Vmax,-7)or
InMonthToDate([Calender Year Month],Vmax,-8)or
InMonthToDate([Calender Year Month],Vmax,-9)or
InMonthToDate([Calender Year Month],Vmax,-10)or
InMonthToDate([Calender Year Month],Vmax,-11)
,Billing_Header.Billing_Header.Net_Value_Inv))

Vmax is variable

Vmax = Monthname(max([Calender Year Month]))

i click on 2008 JULY i become result as follows:

August 2007 100

September 2007 150

October 2007 180

November 2007 185

December 2007 129

January 2008 157

February 2008 147

March 2008 198

April 2008 247

May 2008 258

June 2008 292

July 2008 178

[Anonymous]

Hi Shideh,

Set analysis is your friend for this.

Basically something like this:

sum( {1<Year = {">1978<2004"}>} Sales )

Instead of hard coding 1978 and 2004 you want to pick out the selected value, 2008 July, (or rather the underlying date value).

For the lower bound of the range you can use the date value in combination with addmonths() to subtract 12 months from the selected period thus giving you a range from 12 months back up until the selected value. There are examples of how to do a range selection with set analysis on the forums.

[#3 · Accepted solution]

Hi Jsn,

Thanks for your Help.

Regards

Shideh

[#4]

Hi Jsn,

I have a formula in Pivotable for the L12 Month :

Sum({1<[Kalender.Kalender.Eindeutiger Monat]= {"$(='<='&Only([Kalender.Kalender.Eindeutiger Monat])&'>'&Only(date(addmonths([Kalender.Kalender.Eindeutiger Monat],-12),'MMM-YYYY')))"} >} Billing_Header.Billing_Header.Net_Value_Inv)

It works Ok, but when i filter for just one Customer, it show the same value as for all Customer.

i remove 1 and replace with $. it show just one Month not L12 Month. the value is just for one Month

i select in Calender 2009 March for the Sales of Last 12 Month (March-2009). i will see the April-2008 to March 2009

see blow (the formular exclude 1)

Sum({$<[Kalender.Kalender.Eindeutiger Monat]= {"$(='<='&Only([Kalender.Kalender.Eindeutiger Monat])&'>'&Only(date(addmonths([Kalender.Kalender.Eindeutiger Monat],-12),'MMM-YYYY')))"} >} Billing_Header.Billing_Header.Delivery_Charges)

March-2009 180

do you have other Idea?

Best regards,

Shideh