Sum of Rows for Pivot table and Aggregation month wise for YTD.

[Anonymous]

Hi,

I have a situation where i.m stuck with three things :

1> Sum of rows to display at pivot table total (When selected partial sum)

2> total not to be shown for Vol Var mt field.

3> Clients YTD requirement is different . He want YTD Of June as FTM of Apr + FTM of May + FTM of June

(We have calculated FTM in formulas for Vol Var Ut, Vol Var , Sp Var , Ex Var).

Those are for FTM I need help with Discovering YTD as per clients requirements.

Please refer dummy app for better understanding. And thanks in advance

[#2]

Hi,

You have to create two variable vStartDate and vEndDate.

vStartDate= Date(YearStart(Max(@_Date),0,4),'DD/MM/YYYY')

vEndDate = Date((Max(@_Date)),'DD/MM/YYYY')

YTD Expression = sum({<@_Date={">=$(vStartDate)<=$(vEndDate)"},Year=,Month=,Quarter=>}Sales)

Note-In calendar you have to create fiscal_year and Fiscal_month.

Thanks,

Sukamal Naskar

[#3]

Hi,

Apr Expr - sum({<Year={"$(=Max(Year))"},Month={'Apr'} >},Month={'Apr'} Sales))

May Expr - sum({<Year={"$(=Max(Year))"},Month={'May'} >},Month={'Apr'} Sales))

Same for other month also.

[#4]

Please ignore earlier message

Apr Expr - sum({<Year={"$(=Max(Year))"},Month={'Apr'} >}Sales))

May Expr - sum({<Year={"$(=Max(Year))"},Month={'May'} >}Sales))

Same for other month also.

[Anonymous]

Hi Sukamal,

Thats basically hardcoding which we need to do in case of user selection.

If you can open attached qvw you will get understanding about given formulas. We need looping structure or some other way to achieve it.

Thanks for revert.