Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
Hi Jsn,
Thanks for your Help.
Regards
Shideh
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