topic Pivot table Help ? in QlikView

Pivot table Help ?

Sun, 18 Nov 2012 21:01:31 GMT

I need your Help please,

My Problem is the following :

Client N°	Réference	Débit	Crédit
Client1	Ref1	100
Client1	Ref2		200
Client1	Ref3	150
Client1	Ref4		125

I want to calculate the new solde after each line per client.

For each client i have a initial Solde

New solde solde = Initial solde + sum(Crédit) - sum(Débit) for the first line

after that NewSolde= Newsolde + sum(Crédit) - sum(Débit) for each Client.

How can i do this with Pivot table ?

Thanks in advance for your Help.

Re: Pivot table Help ?

Lee_Matthews — Sun, 18 Nov 2012 23:05:13 GMT

You can possibly do this using the AGGR function. This function allows you create sub-totals based on a custom aggregation within each row. It does not reference the row above as you describe, but may allow you to create aggregation logic that will mimic what you want. But it would rely on the table having a specific sort order for the logic to work that way you want. Though I suspect your logic will need to be based on some natural sequence anyway.

That said, I think the better (and simpler) approach would be to build the calculation in your load script using the Peek function.

Re: Pivot table Help ?

Lee_Matthews — Mon, 19 Nov 2012 00:31:24 GMT

Actually, this excellent blog post by Elif may provide the solution if you want to do it in the chart: http://community.qlik.com/blogs/qlikviewdesignblog/2012/10/12/accumulative-sums.

Althoug I would still probably do it in the load script as suggested above.