Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Not applicable

Acuumlated Per Month and Year

Hi for all

I load a table with this fileds

LLave_Movimiento_Acum --> Is a Key formed per AccountID_Year_Month

Accountnum --> AccountID

Año_Acum --> Year

Mes_Acum --> Month

Importe --Value

I need create a filed with the Accumlated from months per year in load script

An examplo for the final table

-----

LLave_Movimiento_AcumAccountnumAño_AcumMes_AcumImporteAcumm
1020-201101102020111100100
1020-201102102020112100200
1020-201103102020113100300
1020-201104102020114100400
1020-201105102020115100500
1020-201106102020116100600
1020-201107102020117100700
1020-201108102020118100800
1020-201109102020119100900
1020-20111010202011101001000
1020-20111110202011111001100
1020-20111210202011121001200
1050-201101105020111200200
1050-201102105020112200400
1050-201103105020113200600
1050-201104105020114200800
1050-2011051050201152001000
1050-2011061050201162001200
1050-2011071050201172001400
1050-2011081050201182001600
1050-2011091050201192001800
1050-20111010502011102002000
1050-20111110502011112002200
1050-20111210502011122002400

-------------------------------------------

Tags (4)
1 Solution

Accepted Solutions
MVP
MVP

Acuumlated Per Month and Year

Yes, your input table INPUT doesn't have this field. That's the new field we create which will hold the accumulated value for Importe (You could rename it of course, in both places).

We can reference this field with peek(), even if it's not part of the input record, because peek() accesses the resident table we create in QV.

View solution in original post

4 Replies
MVP
MVP

Acuumlated Per Month and Year

Hi,

you could use peek() function to retrieve a field value from previous record, this allows you to accumulate, like

INPUT:

LOAD LLave_Movimiento_Acum,

     Accountnum,

     Año_Acum,

     Mes_Acum,

     Importe,

     Acumm as Acumm_Ref

FROM

[http://community.qlik.com/thread/39560]

(html, codepage is 1252, embedded labels, table is @1);

RESULT:

LOAD *,

if(peek(Accountnum)=Accountnum and peek([Año_Acum])=[Año_Acum],peek(Acum_Importe)+Importe,Importe) as Acum_Importe

resident INPUT order by Accountnum, [Año_Acum] asc, [Mes_Acum] asc;

drop table INPUT;

As I understood your request, I start the accumulation for each account and per year. If you don't want to reset the accumulation on a year change, just remove the "and peek([Año_Acum])=[Año_Acum]" from above if statement.

Hope this helps,

Stefan

Not applicable

Acuumlated Per Month and Year

The answer has a problem becose in my input table i don't have the Acum_Importe Field from this paort of load expression "peek(Acum_Importe)+Importe" or the correct load expression is "peek(Importe)+Importe"?

MVP
MVP

Acuumlated Per Month and Year

Yes, your input table INPUT doesn't have this field. That's the new field we create which will hold the accumulated value for Importe (You could rename it of course, in both places).

We can reference this field with peek(), even if it's not part of the input record, because peek() accesses the resident table we create in QV.

View solution in original post

Not applicable

Acuumlated Per Month and Year

Thanks swuehl works fine for me app!!