Skip to main content

Design

The Design blog is all about product and Qlik solutions, such as scripting, data modeling, visual design, extensions, best practices, and more!

Jennell_McIntire
Employee
Employee

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.

8,456 Views
geetaalhan
Creator
Creator

good post .

0 Likes
8,456 Views
Marcio_Campestrini
Specialist
Specialist

Thanks for posting

0 Likes
8,456 Views
qlikmpate0
Creator II
Creator 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.

8,456 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
8,456 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
8,455 Views
nick_scott
Contributor III
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.

6,596 Views
krishna_venkata
Contributor II
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
6,596 Views