topic Accumulated value in QlikView

Accumulated value

ecolomer — Sun, 07 Feb 2016 13:15:18 GMT

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?

Re: Accumulated value

marcus_sommer — Sun, 07 Feb 2016 13:23:57 GMT

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

Re: Accumulated value

ecolomer — Sun, 07 Feb 2016 14:07:24 GMT

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

Re: Accumulated value

marcus_sommer — Sun, 07 Feb 2016 14:15:21 GMT

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

- Marcus

Re: Accumulated value

MK_QSL — Sun, 07 Feb 2016 15:09:44 GMT

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

Re: Accumulated value

ecolomer — Sun, 07 Feb 2016 15:42:21 GMT

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

Re: Accumulated value

MK_QSL — Sun, 07 Feb 2016 15:44:26 GMT

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

Re: Accumulated value

ecolomer — Sun, 07 Feb 2016 16:00:20 GMT

The last column are data expected

Re: Accumulated value

petter — Sun, 07 Feb 2016 16:26:27 GMT

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

Re: Accumulated value

marcus_sommer — Sun, 07 Feb 2016 16:27:18 GMT

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

Re: Accumulated value

MK_QSL — Sun, 07 Feb 2016 16:31:57 GMT

Data:
LOAD 
  Empresa, 
  Cta_Cod, 
  Year, 
  Month, 
  Tmonth 
FROM
qv969213.xlsx
(ooxml, embedded labels, table is Hoja1);


Load 
  Empresa, 
  Cta_Cod, 
  Year, 
  Month,
  Tmonth,
  If(RowNo() = 1, Tmonth, If(Cta_Cod = Previous(Cta_Cod),RangeSum(Peek('Accumulated'),Tmonth),Tmonth)) as Accumulated
Resident Data
Order By Cta_Cod, Year, Month;


Drop Table Data;

Re: Accumulated value

ecolomer — Sun, 07 Feb 2016 17:05:20 GMT

Thank you very much for your help.

I have still a detail to be resolved.

I would like to have a record for each set Cta_Cod, Year, Month, even though there are no movements of the months.

Re: Accumulated value

MK_QSL — Sun, 07 Feb 2016 17:51:53 GMT

Check this..

Re: Accumulated value

ecolomer — Sun, 07 Feb 2016 18:05:01 GMT

Perfect.

Thank's

MUCHAS GRACIAS.

Saludos,

Enrique Colomer