Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Employee
Employee

How IntervalMatch Solved My Profit and Loss Dilemma

Of the 2 ½ years I have been with QlikTech, I have never had to use the IntervalMatch prefix in any of my scripts but I recently found out how powerful it can be.  I was tasked with creating a Profit and Loss (P&L) statement in QlikView based on the format outlined in this Excel file.

PandL in Excel 2.png

  • The “s” in the Exec P&L Level column indicates blank rows
  • The “c” in the Exec P&L Calculation column indicates rows where a calculation needs to be performed
  • The Exec P&L Heading column stores the headings that are to be used in the P&L statement
  • The ExecPLStart and ExecPLEnd columns indicate the range of rows that are to be summed

Who would have thought something as simple as IntervalMatch would solve my problem?  I have always said that I learn something new about QlikView every day and this day was no different.  I was able to load my master account information, my account balance information and then perform the IntervalMatch, using the reporting code field from the master account table, to get everything in sync so that the correct rows were summed in the P&L statement.  Then all I needed to do was create my chart (a straight table) in QlikView.  In the end my chart looked like the image below and I was quite pleased.

pl large.jpg

I wrote a technical brief about how I completed this task.  I know this is just one of the many ways IntervalMatch can be used.  I am sure in the future I will learn some of the other ways.

8 Comments
Not applicable

This is great! Thanks for posting.

2,058 Views
geetaalhan
Contributor

good post .

0 Likes
2,058 Views
Marcio_Campestrini
Valued Contributor

Thanks for posting

0 Likes
2,058 Views
qlikmpate0
Contributor II

Hi Jennell,

I'm new to profit and loss sheets but i have an example where I do not use spreadsheets but data from a database how can I then incorporate the excel spreadsheet you have for the calculations,spaces and interval match?

Apologies again for my lack of understanding.

2,058 Views
Not applicable

That's great! I have a question though. Would it work with pivot table instead of streight table? And what if I wanted to ad additional dimension (let say "AccountDescription", from Your table), and instead of 7 expressions, I'd prefer one ( =sum(Amount) ), but then added dimensions in vertical: FiscalYear and Month.

Would it work in such a case? The effect I want to achieve is expandable pivot like this:

PnL.jpg

Is it possible to do so with the above method? Or if not, is there other way?

Thanks in advance.

0 Likes
2,058 Views
Not applicable

Ok I answered myself 🙂 - Yes It will work!

a.png

But then yet another problem occourred - how to prevent calculated rows (marked with grey) to be expandable.

The only solution I've figured out was to change a little bit formulas of next dimensions:

=if(calculation='c','',AccountGroup)

and

=if(calculation='c','',Account)

Now the calculated rows are still expandable, but they show nothing expanded.

But when one expands it, amounts are disappearing  (like in first row).

Any idea how to fix that?

0 Likes
2,058 Views
nick_scott
New Contributor III

Hi Jennell,

Cash Flow Example

Your absolutely right, the IntervalMatch approach is the best for creating a Profit & Loss (Income Statement).

I have also used this approach to extend the financial statements to include a Cash Flow.

Cash Flow Statement (with Income Statement & Balance Sheet)

It requires a little more knowledge about the Chart Of Accounts and the financial transactions, but it might be useful for anyone creating financial statements,

Discover more,

Nick Scott.

2,058 Views
krishna_venkata
New Contributor II

Hi Jenell ,

i know it is a quite a old post but it is relevant even today.

do you have any way to further drill down such as what account constitutes Gross Sales etc. i am in need of an approach to display the various sales accounts below gross sales to show the splitup and distributions.

let me know if you have any.

0 Likes
2,058 Views