QlikSense Profit and Loss Statement without an extension

[robert99]

[robert99 · Accepted solution]

This is how I set up a profit and loss account in QS cloud business (where extensions can' t be used)

I did something similar many years back when starting on my Qlik journey but forgot how I did it. And no longer had access to the App. So came up with this. 

And I prepared a word document this time. That I have attached

I'm sure there are improvements that could be made but this worked very well. I used this approach to set up three different Profit and Loss accounts and a balance sheet option.

[Jogo]

Hi

How did you set up the calculations for the GM% Sales ?

Thanks

Jo

[robert99]

Hi Jo

This is quite old but will reply just in case anyone else wants to know. Its quite tricky involving a 2 step process

1 I set up the total in Script . This only includes SALES and PURCHASES + DIRECT EXPENSES = The Gross Margin. So even though the expression below included Overheads and Other these 2 categories are excluded in the script

Add_TradingGP%:
Concatenate(Excel)
LOAD
'Group2' as ReportType ,
Code ,
dual('GM% Sales',399)as NLRepTitlePLGroup2,
'White' as BGTextColour,
'Black' as FontTextColour ,
'White' as BGMetricColour,
'Black' as FontMetricColour ,
'PercentA' as Format
RESIDENT ReportLoad
where match(NLReportHeaderPL,'Sales','Purchases','Direct Expenses') ;

2 Expression

The relevant section that don't apply have been crossed out. 

Only Gross Margin divided by Sales is applicable. But I had to include an expression that covered all percentages

Another way to do this would be to have many expression for all percentages. One for GM% etc. If (Format = 'Dollar' , then if format = 'PercentGM' ETC 

if(Format = 'Dollar',
num (
-SUM({<NL_PLorBS = {"P&l"} , CanonType = {AccPeriod} , ReportType = {Group2} //,fYear = {$(=max(fYear)-1)}
>}ActualValue),
'£#,##0;-£#,##0'
)

,//else
num(
(-SUM({<NL_PLorBS = {"P&l"} , Format ={PercentA}, NLReportHeaderPL = {Sales, Purchases,"Direct*",Overheads, Other} ,CanonType = {AccPeriod} , ReportType = {Group2} >} ActualValue)
+SUM({<NL_PLorBS = {"P&l"} , Format ={PercentB}, NLReportHeaderPL = { Purchases,"Direct*",Overheads, Other} ,CanonType = {AccPeriod} , ReportType = {Group2} >} ActualValue))

/
-SUM({<NL_PLorBS = {"P&l"} , NLReportHeaderPL = {Sales} ,CanonType = {AccPeriod} , ReportType = {Group2} >} ActualValue)
,'0.0%')
)

EDIT With the new Set Analysis this would work

{<NL_PLorBS = {"P&l"}, CanonType = {AccPeriod},ReportType = {Group2} >}
if(Format = 'Dollar',
num (-SUM(ActualValue),'£#,##0;-£#,##0' )
//else for PercentA or PercentB. (PercentB only applies to Overheads % Sales) 
, num(
(-SUM({< Format ={PercentA} >} ActualValue)  
+SUM({< Format ={PercentB}, NLReportHeaderPL = {Overheads} >} ActualValue)) 
/
-SUM({< Format = {PercentA,PercentB},NLReportHeaderPL = {Sales}>} ActualValue)
,'0.0%')
)

[robert99]

You could also use ValueList() for a simple P+L. As shown below

But make it Sales COS and Margin

Dimension

=ValueList('Sum 2014','Margin % 2014','Sum 2015')

Expression

=IF( ValueList('Sum 2014','Margin % 2014', 'Sum 2015')='Sum 2014',

num( Sum ({<Year *= {2014}>}

[Invoiced Quantity] * [OrderPrice] *(1-[Line Discount %]) * (1-["Invoice Discount %"]))
,'£#,##0;-£#,##0')
,
IF( ValueList('Sum 2014','Margin % 2014', 'Sum 2015')='Sum 2015',

num( Sum ({<Year *= {2015}>}

[Invoiced Quantity] * [OrderPrice] *(1-[Line Discount %]) * (1-["Invoice Discount %"]))
,'£#,##0;-£#,##0')
,

num(
SUM ( {<Year = {2014}>} Margin) / SUM ( {<Year = {2014}>} Sales£),'0.0%') ))

Background Color

=IF( ValueList('Sum 2014','Margin % 2014', 'Sum 2015')='Margin % 2014',Black(),white())