topic Re: QlikSense Profit and Loss Statement without an extension in App Development

QlikSense Profit and Loss Statement without an extension

robert99 — Sat, 16 Nov 2024 21:49:35 GMT

Re: QlikSense Profit and Loss Statement without an extension

robert99 — Tue, 27 Nov 2018 10:37:09 GMT

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.

Re: QlikSense Profit and Loss Statement without an extension

Jogo — Mon, 23 Aug 2021 23:06:22 GMT

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

Thanks

Re: QlikSense Profit and Loss Statement without an extension

robert99 — Mon, 03 Oct 2022 04:14:31 GMT

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%')
)

Re: QlikSense Profit and Loss Statement without an extension

robert99 — Fri, 23 Sep 2022 04:32:13 GMT

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())