cumulative issue?

[#1]

Hi All,

I have Calucated Doc_Amount up to till date?

Here If I am Selecting any Year & any Month I getting Doc_Amount Correct..

I selected may -2014 up to till May Ending I am getting the Doc_Amount Correct..

I selected Apr -2014 up to till Apr Ending I am getting the Doc_Amount Correct..

I selected mar -2014 up to till mar Ending I am getting the Doc_Amount Correct..

like this So on.....

Here I want If I select any month Apr-2014, From that Current month onwards, I need to get last 12 months Avg?

Sum(Apr-2014,mar-2014,..............May-2014) / 12 is my Last 12 months Avg ?

Regards,

Helen

[rubenmarin]

Hi Helen, is each Doc_Amount per month the cummulative sum of the last 12 months?

When you say las 12 moths avg is meant to be the average of each month alone?

If so, you only need to use Doc_Amount/12

[#3]

Till ENd of Each month I am getting the Doc_Amount ?

I have to get Last 12 months Avg of Doc_Amount?

This is my My doc Amount Expression:

=sum({<TD_DOC_DT={"<=$(=max(TD_DOC_DT))"},TD_DOC_DT_Year=,TD_DOC_DT_Month=,TD_DOC_DRCR_FLAG={'D'}>}TD_DOC_AMT)-

sum({<TD_DOC_DT={"<=$(=max(TD_DOC_DT))"},TD_DOC_DT_Year=,TD_DOC_DT_Month=,TD_DOC_DRCR_FLAG={'C'}>}TD_DOC_AMT)

+

sum(OPENING)

[rubenmarin]

Ok, I don't really undesrtand but I will make a wild guessing, supposing TD_DOC_DT is the date and TD_DOC_DRCR_FLAG is Credit/Debit or something like that.

Can you try:

(sum({<TD_DOC_DT={">=$(=Addmonths(max(TD_DOC_DT), -12))<=$(=max(TD_DOC_DT))"}, TD_DOC_DT_Year=,TD_DOC_DT_Month=,TD_DOC_DRCR_FLAG={'D'}>}TD_DOC_AMT)-

sum({<TD_DOC_DT={">=$(=Addmonths(max(TD_DOC_DT), -12))<=$(=max(TD_DOC_DT))"}, TD_DOC_DT_Year=,TD_DOC_DT_Month=,TD_DOC_DRCR_FLAG={'C'}>}TD_DOC_AMT))/12

Not sure if you want to add the "+ sum(OPENING)"

Hope this works.If it's not, probably I need a sample to give more help.

[#5]

Hi Ruben,

PFA..

Sum(OPENING) is the  some debtor amount which has to be added for the customer.

Sample O/P Should be:

Select Customer No: 100950

2014-July

DOc MAount              Last 12 Months

3282049.29                      1697138

2014-May

DOc MAount              Last 12 Months

1464890.81                    1439224

2014-May

DOc MAount              Last 12 Months

2182339.81                    1437235.14 !

[#6]

I am not getting last 12 months Avg as correct value ? plz check this

[Kushal_Chawda]

Please see the below link

http://community.qlik.com/thread/139496

[#8]

HI  Kush,

There it was Sales, I have to take Sales for Each & every Month, there it was fine...

Here Amounts I need to take till date for Each & Every month....

If I select May-2014, then Last 12 months Avg Will be taken as like this....

Sum(<=May2014 + <=Apr2014 + <=Mar2014 ............+<=Jun2013)  / 12...

Can you please go through the app, Sample O/P also i have mentioned there....

[#9]

Any Ans on above methodology..

[rubenmarin]

Hi Betty, sorry, I was busy. I think I start to understand what you want, to get the value of each Doc_amount you sum all values from the begining and you want the average of that cummulative values, not for that last 12 months alone.

I don't get a quick answer to optimize your methodology so go on or wait untill some other guy gives any tips.

FYI: For some accountant documents I create the cummulative value in script, so it's easier to use un graphics but I don't know if it will work with your data granularity.