Pivot table where each row has different calculation logic.

[priscilarubim]

Hi Community,

What is the best approach to build a P&L report as a pivot table in Qlik Cloud (columns: Actual / Budget / Prior Year) when each row follows a completely different calculation rule, without ending up with a single enormous measure expression?

In Crystal, each row was hardcoded with its own formula. In Qlik, I need to find a scalable way to handle this, ideally driven by a metadata/mapping table that defines each line's account range, formula type, and sign.

Has anyone tackled this pattern before?

Any examples or approaches are welcome. Thanks!

[Vegar]

I think i have i solution to this usecasd here: https://arntsen.se/projekt/rapport. Github source: https://github.com/veglar/qliksense-sublib/blob/main/data_load_script/sublib.rapport.qvs

• Account intervals. Even overlapping like `4000..4099` on one row and `4000..4999` on another.
• Reference other rows in your in your configuration both adding and division (i don't remember if it handles subtraction,  I think I solved that by defining multiple intervals that excluded what I did not want to include)
• It also handle parallel account plans. Let's say you want to combine the pnl for two companies ("Bolag") when they have a different account setup.

Back when i wrote this I was focusing on a Swedish audience, so the code and comments are in Swedish. I have not looked at it in a while, but if it is found useful then it might find some time to revamp a bit on it and at least change it into English. 

[priscilarubim]

I'll take a look, maybe I can get help from some AI to translate. Thank you 🙂

[Or]

I don't know if it's best-practice, but I use an external file for this with "generic" formulas.

It'll typically look something like this:

The formulas tend to run something like this (Note that this is QV, but the same logic applies in QS):

if(only(PNL_ISPERCENT)=1,num(if(sum({< [Period Name]=>} if(Type = 'F',1))>0,1)*
PNL_SIGN*(SUM({<[Period Name]= >}$(Amount_Forecast))
+
sum({<Category=p(Category) ,[Period Name] =,[Period Year] = {$(=SELECT_YEAR)} >} if([Expenses Category Number] > 20,$(Amount_var),0)))
/
sum({<[Expenses Category Number] = {10}, [Period Name]= >}total $(Amount_Forecast))
,'#,##0.0%'),num(SUM({<[Period Name]= >}$(Amount_Forecast))
+
sum({<Category=p(Category) ,[Period Name] =,[Period Year] = {$(=SELECT_YEAR)} >}
if([Expenses Category Number] > 20,$(Amount_var),0)),if(PNL_REVERSE=1,'-#,##0;#,##0;-','#,##0;-#,##0;-')))

With the formatting applied in the relevant settings for styling (background, bold, etc).

This can be simplified in many cases e.g. the set analysis will probably be mostly unnecessary for most use cases, this app has a always-one-selected rule on period and deals with budget forecast as well as the actual PNL. It has the added advantage that you can pivot and each section contains the underlying rows used to calculate it (which means rows are duplicated, of course, but that's intentional). 

[Lech_Miszkiewicz · Accepted solution]

Hi @priscilarubim 

This is typical P&L layout based on simple grouping of accounts and few extra tweaks you would have to use to put % lines. To answer your question we would need to know what your data source is and how you model your data. I prefer to think how I want to work with my data model before trying to write any expression so I would make sure signs for amounts are already taken care of and we have proper debits/credits amounts in journals.

The usual source of data for the above scenario (except %) is GL amount aggregated by accounts. Then all you need is just grouping of those accounts with corresponding label you want to use to display a measure and in reality you dont even need to do any gymnastics as values in GL should already have correct debit/credit signs hence your measure should only be Sum(GL_Amount) + set analysis for CY and LY periods typically based on reference calendar. 

For the % measures you need additional identifier as for those you may need to create IF() statement and use different number format. Properly built reference calendar will handle CY and LY scenario and budget obviously just concatenated to fact table. 

This is a stock - standard example we were using for many years and many clients and it works a treat and is super simple. Attaching sample app with the principles and example based on Ctrl+0+0 data.

[priscilarubim · Accepted solution]

Unfortunately, I can't show more than I've already mentioned because it's sensitive data, but what was done was basically what you described. Thank you for responding. 🙂