# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
cancel
Showing results for
Did you mean:
Not applicable

## How to sum of specific categories using a straight/pivot table (Financial Reports_

Hi all,

I had some trouble to formulate an appropriate title for my problem, but I will try to describe the problem as well as possible.

What I try to realize is the following. I have a number of General Ledger entries that I want map to certain categories. This is quite easy to realize when using the ApplyMap() function.

 GL_Account GL_Account_Description 7301501 Total revenues X 7301502 Total revenues X 7301503 Total revenues X 7400302 Total revenues Y 7400303 Total revenues Y 7400304 Total revenues Y 7400305 Total revenues Y 7400304 Revenues Z 7400305 Revenues Z 7400306 Revenues Z 7701900 Revenues other 7701901 Revenues other 7701902 Revenues other 7760054 Services X 7760055 Services X 7760056 Services X 7701900 Services Y 7701907 Services Y 7750000 Services Y 7760054 Services Y 7760071 Services Z 7760072 Services Z 7760073 Services Z 9000002 Services other 9000003 Services other 9000005 Services other

However, if I want to sum specific categories of those GL Accounts (e.g. "Total net revenues" , "Total Services" , "Total Gross Margin") and try to visualize this by using a straight table, it seems to be quite hard.

 Description Calculation Total revenues X 1 Total revenues Y 2 Revenues Z 3 Revenues other 4 Total net revenues 5 = 1 + 2 + 3 + 4 Services X 6 Services Y 7 Services Z 8 Services other 9 Total Services 10 = 6 + 7 + 8 + 9 Gross margin X 11 = 1 + 6 Gross margin Y 12 = 2 + 7 Gross margin Z 13 = 3 + 8 Gross margin other 14 = 4 + 9 Total gross margin 15 = 5 + 10

I know there a some alternatives to realize this ( e.g. using set analysis in expression, seperate objects, custom objects, etc). However, it is essential that the client has the opportunity to export the data to excel into one single overview.

Another option I got now is to pivot the fields into columns using a cross-table function and calculate the additional fields needed. I am quite unhappy with this option, because this requires lots of coding with potential errors as far more categories are need than shown in the example. Also, in terms of maintanability this option is quite sustainable as more categories could be added in the future.

I am wondering which alternative methods are there. Also links to related topics are very welcome.

Thank you!

Regards,

Stefan.

1 Solution

Accepted Solutions
Not applicable
Author

Hi Stefan,

I created a sample application in which a template is used to set up the layout for your table/pivot. You can set the background color, bold/italic/underline. You also define your formulas in here and during reload a variable is created that is used as the expression in your table/pivot. The nice thing about the pivot is that you can expand every level to show the corresponding GL-accounts.

It's also possible to send the result to Excel.

Kind regards,

Roel van Wijk

2foqus

12 Replies

Perhaps this blog post helps: How IntervalMatch Solved My Profit and Loss Dilemma

talk is cheap, supply exceeds demand
MVP

Hi Stefan,

We have created groups for such gl master and used for financial reports PFA I have attached sample Hope this may help you.

Vikas

If the issue is solved please mark the answer with Accept as Solution.
If you want to go quickly, go alone. If you want to go far, go together.
Not applicable
Author

Tnx for the reply. I think your response offers a solution with the mapping, but unfortunately this solution doesn't deals with the visualization of calculated fields.

Not applicable
Author

Hi Gijsbert,

Tnx for the reply, I also found the same blog. I think the blog is very useful and brings me almost to the desired solution. Only thing which is not covered in this blog is the way the following fields are calculated:

 Gross margin X 11 = 1 + 6 Gross margin Y 12 = 2 + 7 Gross margin Z 13 = 3 + 8 Gross margin other 14 = 4 + 9

As far as I understand the solution makes a calculation for totals based on ranges.

I need to think for a solution how this works out for "Gross Margin X","Gross Margin Y", "Gross Margin Z" and "Gross Margin Other" as those are not calculated based on a range but on individual fields instead.

.

Read the Technical Brief that's linked to in the blog post: How to Create a Profit and Loss Statement in QlikView.

talk is cheap, supply exceeds demand
Not applicable
Author

Hi Stefan,

I created a sample application in which a template is used to set up the layout for your table/pivot. You can set the background color, bold/italic/underline. You also define your formulas in here and during reload a variable is created that is used as the expression in your table/pivot. The nice thing about the pivot is that you can expand every level to show the corresponding GL-accounts.

It's also possible to send the result to Excel.

Kind regards,

Roel van Wijk

2foqus

Not applicable
Author

Hi,

please see attached, you can potentially solve something like this with a GL account grouping in a side table (rather than a true account hierarchy).

I haven't added in all your different lines but hopefully it gives you the idea

hope that helps

Joe

Not applicable
Author

Hi Joe,

Tnx for commenting on this post. Very simple solution which also works pretty well. I will consider this option. The solution will require some effort to prepare the right GL Account Mapping but if its only required once then its certainly worth considering. Thank you.

Not applicable
Author

no problem, I have ordered that based on original load, but you would probably want a sort order field in that table to use too.