Profit & Loss Statement - Costumized Partial Sums

[#1]

Dear QlikView Experts,

I am currently working on a specific P&L-Statement. It's a quite hard one, i didn't find a solution for the partial sums yet. But I am sure there must be a solution, because the data model provides information about the partial sums, as the P&L statement is configured in the sourcing system and every row "knows" in which row it needs to be sumed up.

So there is information about the row (row number and description) and an additional column pointing to the partial sum row. The partial sum row however doesn't have its own values.

The yellow rows in the screenshot are examples for the partial sum row. There is information in the config table about these rows (so they can be printed, they have a "row no"), but no values for the "value" column in the data model. But there are "primary" rows, pointing in the "Addition to" column to the partial sum row.

Now I am searching for some kind of formula, that tells for example the row "row no= 4010" to sum up all rows with "addition to=4010". I tried lots of IFs and AGGRs but could not manage to find the right expression.

Any ideas? Thank you so much for any help!

[#2]

Hey Dominik,

Usually Partial sums are calculated by Qlikview itself in Pivot tables based on the grouping of dimensions. So just wondering why you would need a separate flag  to identify where to calculate the partial sum. What purpose does it serve.

Also is it possible to attach a sample file(qvw/ source file)

Thanks

AJ

[Colin-Albert]

There is more information on creating financial reports with QlikView here. Straight tables will give more flexibility for controlling totals and formatting than pivot tables.

How to Create a Profit and Loss Statement in QlikView

[#4]

Hi Guys,

first thanks for your answers. I first had to manage some other topics within the QV applications, so sorry for the late reply.

Short update: I now managed to get things working the way I wanted to.

@ Ajay Prabhakaran: The partial sums are highly customized. We have a lot of different P&L statements, actually every user can create his own P&L in the sourcing system, can define own rows and attach accounts to every row. And also, partial sum rows can be created, which sum up a defined set of other rows, self being attached to own accounts or not. The configuration of these P&L are part of the data model.

@Colin Albert: Thanks for the reply. This is quite good material and indicates a rather similar solution that I found for that problem.

I am now doing the changes mainly in the data model. I first load all "source" rows with own accounts, and start a distribution to their sum rows. Therefore I create new records, that add all the accounts from the "source" rows to the partial sums rows. So the partial sums will become "source" rows as well, as I link them to the data.

I need some flags and must perform that step several times (do...loop while), because this distribution process may have several steps (partial sums go into partials sums, that go into partial sums.... etc.).

But now it works fine.

Greetings,

Dominik

[Colin-Albert]

Hi Dominik,

Please mark answers that are helpful or correct as this will help others users searching for the same issue.

Colin

[#6]

Hi Dominik,

Right now am working on P&L statement and i did have the same issue i.e for the subtotals. can you please share the solution(scripting) for creating new records and partial sum rows.

Many Thannks

Sridhar