Profit & Loss P&L calculate Gross Profit % GP% using Janelle's model

[#1]

I am trying to get your solution to work for me, but I am having trouble.  When the calculation below is in my straight table, it returns a null value.  When the calculation is in a text box by itself it calculates correctly.  I tried adding another column so that it would divide each account by Total Sales, but it only worked for the Total Sales line.

Calculation:

if([Exec P&L Heading] <> null(),

if([Exec P&L Level] = 's','',

  if([Exec P&L Percent]='p',

  num(

   sum({$<Month={$(vMaxYTDMonth)},[Account Description]={'Gross Profit'}>} [Actual])

   /

   sum({$<Month={$(vMaxYTDMonth)},[Exec P&L Heading]={'Total Sales'}>} [Actual])

   ,'#,##0.0%')

    ,

   if([PL Sign]=1,

   num((sum({< [Year]={$(vYTDYear)}, Month={$(vMaxYTDMonth)} >} [Actual])*-1)/1000,'#,##0'),

    num(sum({< [Year]={$(vYTDYear)}, Month={$(vMaxYTDMonth)} >} [Actual])/1000,'#,##0')

    )  )  ) )

As I said above, the  " if([Exec P&L Percent]='p', " portion of the formula works as expected in a text box, but not in my straight table.

What am I doing incorrectly?  Any suggestions are welcome.  Thank you in advance!

[sunny_talwar]

Can you provide a sample to play around with?

[sunny_talwar · Accepted solution]

Try this:

if([Exec P&L Heading] <> null(),

  if([Exec P&L Level] = 's','',

  if([Exec P&L Percent]='p',

  num(

  sum(TOTAL {$<Month={$(vMaxYTDMonth)},[Account Description]={'Gross Profit'}>} [Actual])

  /

  sum(TOTAL {$<Month={$(vMaxYTDMonth)},[Exec P&L Heading]={'Total Sales'}>} [Actual])

  ,'#,##0.0%')

  ,

  if([PL Sign]=1,

  num((sum({< [Year]={$(vYTDYear)}, Month={$(vMaxYTDMonth)} >} [Actual])*-1)/1000,'#,##0'),

  num(sum({< [Year]={$(vYTDYear)}, Month={$(vMaxYTDMonth)} >} [Actual])/1000,'#,##0')

  )

  )

  )

  )

[#4]

That worked!  Thanks @Sunny T!

[muratakkemik]

Hello Sunny,

Could you please send me an example model with some sample data for P&L reporting? I liked so much, i'm gonna work on it?

[sunny_talwar]

Have you seen this? Below thread seem to come with an example, may be something you can play around with

How to Create a Profit and Loss Statement in QlikView