topic Re: Avg issue? in QlikView

Avg issue?

Thu, 16 Oct 2014 06:51:08 GMT

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

Re: Avg issue?

PrashantSangle — Thu, 16 Oct 2014 06:53:03 GMT

Hi,

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

Regards

Re: Avg issue?

Thu, 16 Oct 2014 06:55:05 GMT

Hi,

Since number of months are constant to 12.

you can use the below.

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

Re: Avg issue?

Thu, 16 Oct 2014 07:05:36 GMT

Hi Shruthi,

12 is Fine, but I need to get dynamically that value & didvided by sales is nothing but my avg..

regards,

Helen

Re: Avg issue?

Thu, 16 Oct 2014 07:07:43 GMT

Hi,

in that case just replace Sum by Avg in your expression. It will calculate automatically

Re: Avg issue?

Thu, 16 Oct 2014 07:07:47 GMT

HI Max,

I am getting Incorrect Value..Suggest me any other way..

Regards,

Helen

Re: Avg issue?

Thu, 16 Oct 2014 07:10:52 GMT

Tried but it was not working ..

Re: Avg issue?

mdmukramali — Thu, 16 Oct 2014 07:11:55 GMT

Dear,

can you attach the sample file.

so that we can try to help you.

Thanks,

Mukram

Re: Avg issue?

perumal_41 — Thu, 16 Oct 2014 07:22:30 GMT

Try this one

=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 month([Invoice Date]))

Re: Avg issue?

mr_novice — Thu, 16 Oct 2014 07:26:18 GMT

Sales average per month for the last 12 month?

Re: Avg issue?

Thu, 16 Oct 2014 07:43:04 GMT

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')

Re: Avg issue?

Thu, 16 Oct 2014 07:44:16 GMT

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

Re: Avg issue?

Mon, 20 Oct 2014 14:04:52 GMT

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....

Re: Avg issue?

Mon, 20 Oct 2014 14:06:20 GMT

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

Re: Avg issue?

perumal_41 — Tue, 21 Oct 2014 03:29:03 GMT

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')

Re: Avg issue?

perumal_41 — Tue, 21 Oct 2014 03:29:18 GMT

Hi ,

Try this expression

Re: Avg issue?

Tue, 21 Oct 2014 04:43:31 GMT

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..

Re: Avg issue?

Tue, 21 Oct 2014 05:26:58 GMT

you can try this.

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

Re: Avg issue?

jagan — Tue, 21 Oct 2014 06:04:38 GMT

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]))

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.

Re: Avg issue?

perumal_41 — Tue, 21 Oct 2014 06:16:39 GMT

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')