Accumulated value

[ecolomer]

I have a table with the movements that occur daily (Office, Product, Date, Amount) and I want to get a table with the following contents:

Office, Product, year, month, Accumulated amount.

The accumulated amount is always from the start to the month in question.

What would be the quickest and most efficient way to get it?

TX

[marcus_sommer]

Is the amount of your datas aren't really huge the quickest way would be to connect the data with a master-calendar to get period fields like month and year and then summing simply up per sum(Amount) - and you keep the detail-data.

But of course you could also precalculate them into a table like:

Load Office, Product, year(date) as year, month(date) as month, sum(Amount) as MonthAmount

From YourTable Group By Office, Product, year(date), month(date);

Whereby you will probably have another tables in your datamodel and you will need to link this table assumingly rather with a combined key and instead of using those fields.

- Marcus

[ecolomer]

In this form MonthAmount is total only for this month but I need Sum total cumulative form begin to this month

[marcus_sommer]

To accumulate datas you need Peek() or Previous() ?‌ or within a table-chart above or below: The Above Function.

- Marcus

[MK_QSL]

Use below function's combination to get the desired result..

Resident Load

Peek

RangeSum

While doing resident load use order by Date, office

If you can provide few lines of sample data, would like to create this script for you..

[ecolomer]

Actualy I have this:

RMOV:

LOAD

       Empresa,

       Cta_Cod,

       Year,

       Month,

       Sum(Amount) as TMonth

resident MOVTO

where EsNormal='S'

Group by Empresa, Cta_Cod, Year, Month;

and I have a total by Month in TMonth, but I need an acumulated value aditional from the first month to the month in each one

[MK_QSL]

Provide 10 to 15 lines of data in excel along with the expected result. It's easy for us to answer.

[ecolomer]

The last column are data expected

TX

[petter]

If you are 100% sure that you spreadsheet table is sorted by Year, Month and Cta_Cod you could make the calculation like this:

LOAD
    Empresa,
    Cta_Cod,
    Year,
    Month,
    Tmonth,
    Acum.,
    If( Peek(Cta_Cod) <> Cta_Cod , Tmonth , Peek(Acum) + Tmonth) AS Acum
FROM

    (ooxml, embedded labels, table is Hoja1)
;

I kept the Acum. as a reference ...

[marcus_sommer]

Try it with the following:

RMOV_temp1:

LOAD

       Empresa,

       Cta_Cod,

       Year,

       Month,

       Sum(Amount) as TMonth

resident MOVTO

where EsNormal='S';

RMOV_temp2:

LOAD

       Empresa,

       Cta_Cod,

       Year,

       Month,

       Sum(Amount) as TMonth

resident RMOV_temp1

Group by Empresa, Cta_Cod, Year, Month;

RMOV:

LOAD

       Empresa,

       Cta_Cod,

       Year,

       Month,

       TMonth,

       If(Empresa = peek('Empresa') and Cta_Cod = peek('Cta_Cod'), TMonth + peek('MonthCum'), TMonth) as MonthCum

resident RMOV_temp2

Order by Empresa, Cta_Cod, Year, Month;

It is splitted into several loads because they are often faster then combined into a single load.

- Marcus