Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead 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_AccountGL_Account_Description
7301501Total revenues X
7301502Total revenues X
7301503Total revenues X
7400302Total revenues Y
7400303Total revenues Y
7400304Total revenues Y
7400305Total revenues Y
7400304Revenues Z
7400305Revenues Z
7400306Revenues Z
7701900Revenues other
7701901Revenues other
7701902Revenues other
7760054Services X
7760055Services X
7760056Services X
7701900Services Y
7701907Services Y
7750000Services Y
7760054Services Y
7760071Services Z
7760072Services Z
7760073Services Z
9000002Services other
9000003Services other
9000005Services 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.

DescriptionCalculation
Total revenues X1
Total revenues Y2
Revenues Z3
Revenues other4
Total net revenues5 = 1 + 2 + 3 + 4
Services X6
Services Y7
Services Z8
Services other9
Total Services10 = 6 + 7 + 8 + 9
Gross margin X11 = 1 + 6
Gross margin Y12 = 2 + 7
Gross margin Z13 = 3 + 8
Gross margin other14 = 4 + 9
Total gross margin15 = 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

View solution in original post

12 Replies
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
vikasmahajan

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

Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.
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 X11 = 1 + 6
Gross margin Y12 = 2 + 7
Gross margin Z13 = 3 + 8
Gross margin other14 = 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.

.

Gysbert_Wassenaar

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.