Avg issue?

[#1]

Hi All,

I need to find last 12 months avg sales..

=Sum({<[Invoice Date]={'>=$(=MonthStart(Max([Invoice Date]), -11))<=$(=Date(Max([Invoice Date])))'}>}Sales)

For this Expression I am getting Last 12 Month Sales Value, I need to get Avg of this Sales ?

Regards,

Helen

[#2]

yes..

Now this expr is working fine..

Thanks all..

=num(Avg(aggr(Sum({<[Invoice Date]={'>=$(=MonthStart(Max([Invoice Date]), -11))<=$(=Date(Max([Invoice Date])))'}>}[Invoice Turn Over]),[Invoice Month])),'#,##0.000')

[#3]

=num(Avg(aggr(Sum({<[Invoice Date]={'>=$(=MonthStart(Max([Invoice Date]), -11))<=$(=Date(Max([Invoice Date])))'}>}[Invoice Turn Over]),[Invoice Month])),'#,##0.000')

[#4]

Hi Perumal

I am getting Last 12 Months Avg Sales based on Custcode...

=num(Avg(aggr(Sum({<[Invoice Date]={'>=$(=MonthStart(Max([Invoice Date]), -11))<=$(=Date(Max([Invoice Date])))'}>}[Invoice Turn Over]),CUST_CODE1)),'#,##0.000')

I need to get Last 12 Months Avg Balance Amount on Custcode..

Balance Amount = Sum(ORG Amt) - Sum(ADJ Amt)

=num(Avg(aggr(Sum({<OST_DOC_DT={'>=$(=MonthStart(Max(OST_DOC_DT), -11))<=$(=Date(Max(OST_DOC_DT)))'}>}

sum(OST_FC_ORG_AMT)-sum(OST_FC_ADJ_AMT)),CUST_CODE1)),'#,##0.000')

I am getting Null Value Last 12 Months Avg Balance Amount column....

[#5]

Instead of Sales , I am  Keeping  Some EXpression , Getting Null Value ...HOw to Resolve  ?

[perumal_41]

Hi ,

Try this expression

=num(Avg(aggr(sum({<OST_DOC_DT={'>=$(=MonthStart(Max(OST_DOC_DT), -11))<=$(=Date(Max(OST_DOC_DT)))'}>}OST_FC_ORG_AMT)
-sum({<OST_DOC_DT={'>=$(=MonthStart(Max(OST_DOC_DT), -11))<=$(=Date(Max(OST_DOC_DT)))'}>}OST_FC_ADJ_AMT),CUST_CODE1)),'#,##0.000')

[perumal_41]

Hi ,

Try this expression

=num(Avg(aggr(sum({<OST_DOC_DT={'>=$(=MonthStart(Max(OST_DOC_DT), -11))<=$(=Date(Max(OST_DOC_DT)))'}>}OST_FC_ORG_AMT)
-sum({<OST_DOC_DT={'>=$(=MonthStart(Max(OST_DOC_DT), -11))<=$(=Date(Max(OST_DOC_DT)))'}>}OST_FC_ADJ_AMT),CUST_CODE1)),'#,##0.000')

[#8]

HI Perumal,

its coming but I want to use this Expression in Last 12 Months Avg...

=(Sum({<OST_DRCR_FLAG={'C'}>}OST_FC_ADJ_AMT)-sum({<OST_DRCR_FLAG={'C'}>}OST_FC_ORG_AMT))+

(Sum({<OST_DRCR_FLAG={'D'}>}OST_FC_ORG_AMT)-sum({<OST_DRCR_FLAG={'D'}>}OST_FC_ADJ_AMT))

Help me on this..

[#9]

you can try this.

sum({<[Invoice Date]={'>=$(=MonthStart(Max([Invoice Date]), -11))<=$(=Date(Max([Invoice Date])))'}>}Sales)/12

[jagan]

Hi,

Try like this

sum({<[Invoice Date]={'>=$(=MonthStart(Max([Invoice Date]), -11))<=$(=Date(Max([Invoice Date])))'}>}Sales)/

Count({<[Invoice Date]={'>=$(=MonthStart(Max([Invoice Date]), -11))<=$(=Date(Max([Invoice Date])))'}>} DISTINCT MonthStart(Max([Invoice Date]))

or

sum({<[Invoice Date]={'>=$(=MonthStart(Max([Invoice Date]), -11))<=$(=Date(Max([Invoice Date])))'}>}Sales)/

Count({<[Invoice Date]={'>=$(=MonthStart(Max([Invoice Date]), -11))<=$(=Date(Max([Invoice Date])))'}>} DISTINCT MonthDimensionName)

Hope this helps you.

Regards,

jagan.

[perumal_41]

Hi

Try this

=num(Avg(aggr(((Sum({<OST_DRCR_FLAG={'C'},OST_DOC_DT={'>=$(=MonthStart(Max(OST_DOC_DT), -11))<=$(=Date(Max(OST_DOC_DT)))'}>}OST_FC_ADJ_AMT)

-sum({<OST_DRCR_FLAG={'C'},OST_DOC_DT={'>=$(=MonthStart(Max(OST_DOC_DT), -11))<=$(=Date(Max(OST_DOC_DT)))'}>}OST_FC_ORG_AMT))

+

(Sum({<OST_DRCR_FLAG={'D'},OST_DOC_DT={'>=$(=MonthStart(Max(OST_DOC_DT), -11))<=$(=Date(Max(OST_DOC_DT)))'}>}OST_FC_ORG_AMT)

-sum({<OST_DRCR_FLAG={'D'},OST_DOC_DT={'>=$(=MonthStart(Max(OST_DOC_DT), -11))<=$(=Date(Max(OST_DOC_DT)))'}>}OST_FC_ADJ_AMT))),CUST_CODE1)),'#,##0.000')