Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
Hi
How did you set up the calculations for the GM% Sales ?
Thanks
Jo
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%')
)
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())