topic ACUMULATE ACCOUNTING RECORDS in QlikView

ACUMULATE ACCOUNTING RECORDS

christian77 — Wed, 21 Apr 2010 14:29:04 GMT

What I need is to add another column on the right that accumulates records. IT MUST BE A PIVOT TABLE.

Here is the expresion: sum({1< [Date]={'<=$(vMaxDate)'}>} [Ammount]) I can´t make it work.

Does anybody know how to do it? Any other solution?

Thanks a lot for your attention. (If you make it work I´ll invite you to dinner next time you come to Madrid)

ACUMULATE ACCOUNTING RECORDS

Wed, 21 Apr 2010 14:54:17 GMT

Hi Christian,

please see the attached example to get an idea how it could work for you.

Good luck!

Rainer

ACUMULATE ACCOUNTING RECORDS

christian77 — Mon, 26 Apr 2010 17:00:42 GMT

Thanks Rainer.

Yours is a simple solution, just how I like it, but it does not work. If you determine the values before, any change in history, or filtering, will show incorrect data.

Look at the attached example and tell me, please, what I'm doing wrong, or what I´m not doing.

Thanks.

Call me if you come to Spain. We´ll have dinner anyway.

Christian.

ACUMULATE ACCOUNTING RECORDS

Tue, 29 Mar 2011 15:45:33 GMT

Hi Christian,

Did you finally solve this problem? I have the same problem right now. I would really apreciate to have the solution.

Thanks

Guillermo

ACUMULATE ACCOUNTING RECORDS

christian77 — Thu, 31 Mar 2011 07:36:34 GMT

Hi gmvaldes:

Depends on what you want to do.

If you want the accumulation only in a table or a few tables use accumulate in the expression panel.

You also can accumulate with Above funktion, inter record funcktions, etc.

Example: This one accumulates columns ordered from last to current.

RangeSum(last(Expression),1,(noofcolumns()-columnno()+1)

Example: This one accumulates rows ordered from top to current.

RangeSum(Above(Total (Expression),0,rowno(Total)))

If your table is going to collapse and expand, use a different funktion based in Dimensionality() & SecondaryDimensionality().

Help Column: In a pivot table with several dimensions, add a column

Dimensionality() & ' ' & SecondaryDimensionality() and see what happens. It´ll help you much.

If you want to accumulate for the whole doc, do it on the script. They are many ways.

The easier for me goes like:

Totals:

LOAD

Sum(Money) as MonthlyMoney,

Account as Account,

Year as Year,

Month as Month

RESIDENT Ledger Table

Group by Year, Month, Account

Order by Year, Month;

Calling from resident will save some time I guess.

Now, you need to describe your problem with more detail.

Ok. Chris...