Month Accumulation grouped by Year, Account

[#1]

Hello to everybody. I am a newbie and need help with some raw account data.

I have Amount of Accounts for every month, but the Amount must be accumulated every month for every AccountNumber. How can this be done with QlikView script? Thanks in advance.

[Miguel_Angel_Baeyens]

Hello Konstantin,

Create a new chart, select pivot table, set Month, Year and AccountN as dimensions and

aggr(rangesum(top(Amount, 1, RowNo() -1)), AccountN, Year, Month)

as expression

This should work.

Regards

[#3]

Thank you Miguel, but I need to do this in the scipt..

[suniljain]

Pls check attached qvw .

it contain solution of your probelm.

[Miguel_Angel_Baeyens]

Hello Konstantin,

Add a new table liek the following

LOAD Month, Year, AccountN, Amount, If(AccountN = Previous(AccountN), RangeSum(Amount, Peek('Acum')), Amount) AS AcumRESIDENT RawDataORDER BY AccountN, Month;

If you now create a new chart with Month, Year and AccountN as dimensions and

Sum(Acum)

will see values as expected. YOu can do as well a tablebox with Month, Year, AccountN and Acum as fields, go to properties, Sort by Month and AccountN.

Hope that helps.

[#6]

Yes. Thats it! Thank you, Miguel

[#7]

I found one flaw with this method:
Every Account Number must be present in raw data every month. If Any new Account appears only once, that Amount is not transferred on to the next month, i.e. it does not accumulate... Is there a way to make it do this?

[Miguel_Angel_Baeyens]

Hello Konstantin,

In a tablebox, there is no aggregation, so it will only display actual information. If you don't have more records with that account, it won't be displayed in a table box.

You may try creating a new chart, pivot or straight table, and creating a calendar, so all months are displayed as dimensions in charts, although they have zero as value.

Hope that helps