topic Month Accumulation grouped by Year, Account in QlikView

Month Accumulation grouped by Year, Account

Tue, 21 Sep 2010 09:00:35 GMT

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.

Month Accumulation grouped by Year, Account

Miguel_Angel_Baeyens — Tue, 21 Sep 2010 09:32:19 GMT

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

Month Accumulation grouped by Year, Account

Tue, 21 Sep 2010 09:58:13 GMT

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

Month Accumulation grouped by Year, Account

suniljain — Tue, 21 Sep 2010 11:00:02 GMT

Pls check attached qvw .

it contain solution of your probelm.

Month Accumulation grouped by Year, Account

Miguel_Angel_Baeyens — Tue, 21 Sep 2010 11:14:30 GMT

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.

Month Accumulation grouped by Year, Account

Tue, 21 Sep 2010 12:29:25 GMT

Yes. Thats it! Thank you, Miguel

Month Accumulation grouped by Year, Account

Tue, 21 Sep 2010 17:29:13 GMT

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?

Month Accumulation grouped by Year, Account

Miguel_Angel_Baeyens — Thu, 23 Sep 2010 13:02:03 GMT

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