topic Re: cumulative issue? in QlikView

cumulative issue?

Tue, 28 Oct 2014 13:59:20 GMT

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

Re: cumulative issue?

rubenmarin — Tue, 28 Oct 2014 14:12:59 GMT

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

Re: cumulative issue?

Tue, 28 Oct 2014 14:23:28 GMT

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)

Re: cumulative issue?

rubenmarin — Tue, 28 Oct 2014 14:35:59 GMT

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.

Re: Re: cumulative issue?

Tue, 28 Oct 2014 16:34:12 GMT

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 !

Re: Re: cumulative issue?

Wed, 29 Oct 2014 04:28:00 GMT

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

Re: cumulative issue?

Kushal_Chawda — Wed, 29 Oct 2014 04:38:29 GMT

Please see the below link

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

Re: cumulative issue?

Wed, 29 Oct 2014 04:45:57 GMT

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

Re: cumulative issue?

Wed, 29 Oct 2014 08:17:44 GMT

Any Ans on above methodology..

Re: cumulative issue?

rubenmarin — Wed, 29 Oct 2014 08:55:27 GMT

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.