topic Re: Formula Value Should not change when month / year/ Qtr selected in QlikView

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

vikasmahajan — Fri, 11 Oct 2013 06:05:15 GMT

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

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

Gysbert_Wassenaar — Fri, 11 Oct 2013 07:00:06 GMT

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

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

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

vikasmahajan — Fri, 11 Oct 2013 07:44:52 GMT

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

Vikas

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

Gysbert_Wassenaar — Sat, 12 Oct 2013 09:53:32 GMT

= 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*/

)

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

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