Aug 10, 2012 6:56:45 AM
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.
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.
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.
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 ?
I don't think this is an issue for me anymore.
But thanks for following up.
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.
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].
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 ?
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.
LOAD if([PL Calculation]<>'c', ' '&[Profit and Loss], [Profit and Loss]) as [Profit and Loss], [PL Calculation], PLStart, PLEndFROM $(Path).xlsx
IntervalMatch:LEFT JOIN (PL_Hierarchy)INTERVALMATCH ([Reporting Code])
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.
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.
I already manage to solve the % sales and % exp . Now I only look at red color.