How to Create a Profit and Loss Statement in QlikView

[Jennell_McIntire]

Have you ever created a P&L statement in QlikView?  Well, this technical brief outlines how to create a P&L statement in QlikView 11 using IntervalMatch.

Also check out this blog post.

Thanks,

Jennell

[#2]

Hi Jennell

My user need to export the P&L report from QV to excel. and they don't want to manually high light those text with bond with yellow color back ground . it is possible to modify the expression and paste at black ground color :-

=if([Exec P&L Calculation]='c','<B>')

Hope you can share with me. and thank you for your help in the past.

Paul

[Jennell_McIntire]

Hi Paul,

You can do this by setting the Background Color for the dimension and each expression (except the blank one) to:

=if([Exec P&L Calculation]='c',Yellow())

For the blank column that I used to space the data (4th expression), you can set the Background Color to:

=if([Exec P&L Calculation]='c',Yellow(),RGB(249,249,249))

Hope this helps.

Jennell

[#4]

Hi Jennell

Sorry to trouble you again. Today some one from the forum ask me have i solve the problem on Sales and COGS how to make the variance color look correctly. I told him you did give me the solution which solve the COGS portion you did manage to solve it , but the issue is sales side get affected. May be you can help me to confirm it is possible to achieve by add some more script into it ?

[#5]

Paul,

I don't think this is an issue for me anymore.

But thanks for following up.

David

[#6]

Hi all,

Is there any chance someone has figured out a way to show this P&L using standard P&L sign conventions?

Typically, COGS is a positive number. But Interval Match appears to need a negative to perform the margin calculation correctly.

Thanks,

David

I just figured it out. I used the ExecProfitLoss file, and leveraged the "s" logic in Column A for skipping a space.

I put an "n" (for negative) for the rows where I need the sign flipped. And added an "if" statement after the If "s" statement for Summing the [Amount].

[paulyeo11]

Hi Jennell

Hope you can help to take a look on my below post and give me some hint how can i solve the problem ?

Re: why only when i select company=A all summary row highlighted work fine ?

Paul

[beckerv]

Hi Jennell,

thank you very much for this post, it helped me creating a P&L (and Balance Sheet) overview exactly as I needed it.

However, I would like to add the following:

In order to avoid synthetic keys (which are using up a lot of memory and increase the run time of the script), I used a

left join for the interval match.

e.g.:

PL_Hierarchy:

LOAD
if([PL Calculation]<>'c', ' '&[Profit and Loss], [Profit and Loss]) as [Profit and Loss],
[PL Calculation],
PLStart,
PLEnd
FROM
$(Path).xlsx

IntervalMatch:
LEFT JOIN (PL_Hierarchy)
INTERVALMATCH ([Reporting Code])

LOAD

PLStart,

PLEnd

RESIDENT PL_Hierarchy;

Best regards,

Viktoria

[paulyeo11]

Hi All

Possible to make black ground color Green when Amount is negative ?

Today sudden i realize that i need to create a extra field to differentiate sales and exp,  I have post the question , with in 1 hour i manage to get the solution.

Paul

[#10]

Has anyone solved the % of Sales or Gross Profit as a % of Sales, etc. questions?

I see that the question has been posted multiple times with no response.

Thanks,

David

[paulyeo11]

Hi David

I already manage to solve the % sales and % exp . Now I only look at red color.