How IntervalMatch Solved My Profit and Loss Dilemma

[Jennell_Yorkman]

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.

• 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.

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.

[#2]

This is great! Thanks for posting.

[Anonymous]

good post .

[Marcio_Campestrini]

Thanks for posting

[qlikmpate0]

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.

[#6]

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:

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

Thanks in advance.

[#7]

Ok I answered myself 🙂 - Yes It will work!

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?

[Anonymous]

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.

[Anonymous]

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.