Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Month Accumulation grouped by Year, Account

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.

7 Replies
MVP
MVP

Month Accumulation grouped by Year, Account

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

Not applicable

Month Accumulation grouped by Year, Account

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

suniljain
Honored Contributor

Month Accumulation grouped by Year, Account

Pls check attached qvw .

it contain solution of your probelm.

MVP
MVP

Month Accumulation grouped by Year, Account

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.

Not applicable

Month Accumulation grouped by Year, Account

Yes. Thats it! Thank you, Miguel

Not applicable

Month Accumulation grouped by Year, Account

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?

MVP
MVP

Month Accumulation grouped by Year, Account

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

Community Browser