Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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)
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.
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 !
I am not getting last 12 months Avg as correct value ? plz check this
Please see the below link
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....
Any Ans on above methodology..
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.