Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
Perhaps this blog post helps: How IntervalMatch Solved My Profit and Loss Dilemma
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
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.
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.
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
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
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.
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.