topic Re: How to sum of specific categories using a straight/pivot table (Financial Reports_ in QlikView

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

Fri, 28 Nov 2014 09:29:32 GMT

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.

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

Gysbert_Wassenaar — Fri, 28 Nov 2014 10:08:11 GMT

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

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

vikasmahajan — Fri, 28 Nov 2014 10:10:08 GMT

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

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

Fri, 28 Nov 2014 12:12:44 GMT

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.

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

Fri, 28 Nov 2014 12:26:58 GMT

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.

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

Gysbert_Wassenaar — Fri, 28 Nov 2014 12:35:49 GMT

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

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

Fri, 28 Nov 2014 12:42:10 GMT

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

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

Fri, 28 Nov 2014 12:48:45 GMT

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

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

Fri, 28 Nov 2014 14:02:17 GMT

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.

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

Fri, 28 Nov 2014 14:04:31 GMT

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.

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

Fri, 28 Nov 2014 14:28:30 GMT

Hi Roel,

Thank you for commenting with a great solution.

It looks very promising solution. And as far as I can see now it is also very easy to maintain in case additional business logic need to be added. I will work this solution out and let you know about the results.
Thanks!

Stefan

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

Sat, 29 Nov 2014 09:08:06 GMT

Nice and clean approach!

1 question though, just out of curiosity:

Your expression in the loadscript is this:

'if(dimensionality(),pick(match(ID,|' & concat(distinct ID,'|,|',ID) & '|),' & concat(distinct Formula,', ',ID) &',null()))' as Expression

My question: what does the if/else and dimensionality() function add in this part?

I don't see any difference when I leave this out; perhaps I'm missing something (since I assume you didn't put it in without a reason)?

I thought the dimensionality() returns the number of dimensions (and in case of a pivot table it will increase with 1 for every +-sign you open...

How I now ready this if/else, is that always the if-part will be executed, and the else part will never apply. Maybe I'm missing someting? Perhaps someone can explain the purpose of this.

Kind regards,

Roberto

#edit: after some thinking I think the reason for adding it, is that always a minimum of 1 dimension is needed, meaning that this expression won't work in textboxes, gauges. Buttons etc.

if there are additional reasons, I'd like to hear!

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

Mon, 01 Dec 2014 11:39:11 GMT

For those who also want to use this, I think there should be a small modification in the loadscript:

TEMPTABLE:
LOAD
//Origi 'if(dimensionality(),pick(match(ID,|' & concat(distinct ID,'|,|',ID) & '|),' & concat(distinct Formula,', ',ID) &',null()))' as Expression
'if(dimensionality(),pick(match(ID,|' & concat(distinct ID,'|,|',ID) & '|),' & concat(distinct Formula,', ',ID) &'),null())' as Expression
Resident ReportingTemplate;

>>change: inserted the red-colored ) and removed this ) after null()

As we could al see, the old expression did already work... So you could question what the issue is...

I discovered it when I tested the if (dimensionality() ) part....

I would expect to have the else-part (from the if/else) be active when I put this expression (or variable) into a textbox or some other object without dimensionality. I discovered that the else-part never got activated.

Notice, it is a cosmetic thing probably.

Should there be any comments on this, or if the idea is that this addition is incorrect, please let me know what and why

The final expression I would use is the one below, so you get an error when you try to use it in objects without dimensionality.

'if(dimensionality(),pick(match(ID,|' & concat(distinct ID,'|,|',ID) & '|),' & concat(distinct Formula,', ',ID) &'),''Error: this expression cannot be used in objects without dimensions'')' as Expression