topic Re: Sum of Rows for Pivot table and Aggregation month wise for YTD. in QlikView

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

Anonymous — Wed, 20 Sep 2017 04:20:49 GMT

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

Wed, 20 Sep 2017 09:30:26 GMT

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

Wed, 20 Sep 2017 09:55:44 GMT

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.

Wed, 20 Sep 2017 09:56:43 GMT

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 — Wed, 20 Sep 2017 10:25:17 GMT

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.