Formula Value Should not change when month / year/ Qtr selected

[vikasmahajan]

Hii

I have following formula  in chart  My requirement is  when user will select  Month/Qtr/Year Dimension  values should no chage

Whether it is possible ?

= num( (SUM(AGGR(((SUM({1<FinancialYear=,Quarter=,MonthName=,[Posting Date] ={"<=$(=MonthEnd(Max([Posting Date])))>=01/04/2012"},[Inv Flag]={0,1,3,5}>}QTY_SIGNED) /* Closing Qty of prev month*/

-

SUM({1<FinancialYear=,Quarter=,MonthName=,[Posting Date] ={"<=$(=MonthEnd(Max([Posting Date])))>=01/04/2012"}>}[Sales Invoice Qty]) /*sales Qty from 1/4/12 till date*/

-

SUM({1<FinancialYear=,Quarter=,MonthName=,[Posting Date] ={"<=$(=MonthEnd(Max([Posting Date])))>=01/04/2012"}>}[Sales Bonus Qty]) /*sales Bon Qty from 1/4/12 till date*/

+

SUM({1<FinancialYear=,Quarter=,MonthName=,[Posting Date] ={"<=$(=MonthEnd(Max([Posting Date])))>=01/04/2012"},[Sales Return Type Code]={0}>}[Sales Return Qty]) /*sales Ret Qty from 1/4/12 till date*/

+

SUM({1<FinancialYear=,Quarter=,MonthName=,[Posting Date] ={"<=$(=MonthEnd(Max([Posting Date])))>=01/04/2012"},[Sales Return Type Code]={0}>}[Sales Return Bonus Qty]) /*sales Ret Bon Qty from 1/4/12 till date*/

)

*avg([Stat Rate Inv]))/10000000,[Product Batch Code],[Location Code],[Product Code],[Expiry Date]))

),'############0.#') & ' Crs'

Vikas

[Gysbert_Wassenaar]

Put FinancialYear=,Quarter=,MonthName= as set modifier in the outer sum too.

= num( (SUM({<FinancialYear=,Quarter=,MonthName=>}AGGR(((SUM...etc

[vikasmahajan]

Thanks GW  Can you give full formula to understand me how to apply

Vikas

[Gysbert_Wassenaar · Accepted solution]

= num( (SUM({<FinancialYear=,Quarter=,MonthName=>}AGGR(((SUM({1<FinancialYear=,Quarter=,MonthName=,[Posting Date] ={"<=$(=MonthEnd(Max([Posting Date])))>=01/04/2012"},[Inv Flag]={0,1,3,5}>}QTY_SIGNED) /* Closing Qty of prev month*/

-

SUM({1<FinancialYear=,Quarter=,MonthName=,[Posting Date] ={"<=$(=MonthEnd(Max([Posting Date])))>=01/04/2012"}>}[Sales Invoice Qty]) /*sales Qty from 1/4/12 till date*/

-

SUM({1<FinancialYear=,Quarter=,MonthName=,[Posting Date] ={"<=$(=MonthEnd(Max([Posting Date])))>=01/04/2012"}>}[Sales Bonus Qty]) /*sales Bon Qty from 1/4/12 till date*/

+

SUM({1<FinancialYear=,Quarter=,MonthName=,[Posting Date] ={"<=$(=MonthEnd(Max([Posting Date])))>=01/04/2012"},[Sales Return Type Code]={0}>}[Sales Return Qty]) /*sales Ret Qty from 1/4/12 till date*/

+

SUM({1<FinancialYear=,Quarter=,MonthName=,[Posting Date] ={"<=$(=MonthEnd(Max([Posting Date])))>=01/04/2012"},[Sales Return Type Code]={0}>}[Sales Return Bonus Qty]) /*sales Ret Bon Qty from 1/4/12 till date*/

)

*avg({<FinancialYear=,Quarter=,MonthName=>}[Stat Rate Inv]))/10000000,[Product Batch Code],[Location Code],[Product Code],[Expiry Date]))

),'############0.#') & ' Crs'