Receivables at the end of each month on back end

[#1]

Hello,

          I want to calculate customer receivables at the end of each month on back end script.

For example, if i see data of janaury 2012. It should sum the all the dates data from the minimum date to till the End of janaury 2012.

same for feb 2012, it should show sum of all dates data from minimum date till end of feb 2012.

Thanks

Lavi

[#2]

Any response on this??

[Sokkorn]

Hi Lavi,

Did you try like this yet?

[Data]:

Load

     [MonthYear]     As [Period],

     SUM([AR])     As [TotalAR];

Select MonthYear AR​​​ Group by MonthYear;

It is posible to share your data?

Regards,

Sokkorn

[#4]

Hi Sokkorn,

                        i have already tried this but this will not give me correct result.

If i do group by MonthYear it will show sum of only that particluar monthyear.

For example, if a data is like this

Posting date   Amount(LCY)

10 dec 2011     5

31 dec 2011    6

15 jan 2012     8

30 jan 2012    9

Group by will give result in sum like this

Dec 2011 =11

Jan 2012 =  17

But my expectation is

Dec 2011= 11

Jan 2012 = 11+17=28 and so on.

And also i want this dynamic. If in chart, if i add MonthEndDate as a Dimension. It should show Evolution of Sum.

thanks

Lavi

[Sokkorn]

Hi Lavi,

Let try this

[Data]:

Load

    [MonthYear]     As [Period],

    SUM([AR])         As [TotalAR],

    RangeSum([AR], Peek('AccumulateAR'))    AS [AccumulateAR];

Select MonthYear AR​​​ Group by MonthYear;

Do let me know.

Regards,

Sokkorn

[#6]

Hello Sokkorn,

                       thanks for your response.

When using this statement.

I am getting following error

"Invalid expression"

Have you tried above code??

thanks

Lavi

[Sokkorn · Accepted solution]

Lavi,

Ahhhh my mistake

[Data]:
LOAD *,
    TEXT(MonthName(PostingDate))    AS [Months];
LOAD * INLINE [
PostingDate,   Amount
10-12-2011,     5
31-12-2011,    6
15-01-2012,     8
30-01-2012 ,   9];
[Data2]:
LOAD *,
    RangeSum([Total1], Peek('Total2'))    AS [Total2];
LOAD
    Months     AS Period,
    SUM(Amount)    AS Total1
Resident [Data] GROUP BY Months;

See sample attached file  also.

Regards,

Sokkorn