P&L Set Analysis Challenge

[dyee4613]

Hi Everyone.  

I have a P&L Hierarchy.  This hierarchy has Total Revenue, Total Variable Costs, Total Plant Overhead, Total Administrative Overhead.  I added a new section called Net Income. 

For values, I have it as Sum( [AT Transactions])/100.  However, I want to override the net income total to be the sum of total revenue, total variable costs, total plant overhead, and total administrative overhead.  Here is the set analysis I wrote. 

If ( [Map2] = 'Net Income',

Sum( {$< [Map2] = {'Total Revenue', 'Total Variable Costs', 'Total Plant Overhead', 'Total Administrative Overhead'} > } [AT Trans Amount]),

Sum([AT Trans Amount])/100)

This formula works if I replace the set analysis with a set number like 1,000.  However, when I try to write the set analysis it keeps failing.  Can someone let me know what is causing my set analysis to fail?  

[Lech_Miszkiewicz]

HI @dyee4613 

I know your question is about IF () expressions and little bit of set analysis. What is causing it to fail is simple - you are trying to put unassociated on the same rows. In other words you are trying to say on row with 'Net Income' put row which is X, Y or Z, but those rows are not associated with 'Net Income' so you cannot..

One thing about P&L in Qlik is that effectively it is a grouping of accounts. As such all lables in P&L (in fact whole P&L layout) can be created as a dimension grouping thoses accounts and your measure is then simply sum(Amount). This is very well known common practice so I really encourage you to spend time learning that method as it will simplify your development, your app, It will make your formulas easy and lastly it will make your app flexible. You will be able to click on "Net Income" label and drill down to details of the accounts which make that value.

If you create P&L groupings properly it makes it so much simpler to work with so I encourage you to do that properly. Here is a document describing in baby steps all principles:

https://community.qlik.com/t5/App-Development/QlikSense-Profit-and-Loss-Statement-without-an-extensi...

hope this helps

[dyee4613]

"As such all lables in P&L (in fact whole P&L layout) can be created as a dimension grouping thoses accounts and your measure is then simply sum(Amount)."

This is true if you have a working P&L Map.  I do not have a working P&L map.  I have mentioned to management that we need to create one but to do so would be a very big undertaking as there are over 250k combinations and management isn't entirely sure what information they want to see.  As a result, I found a field that is beginning stages of a P&L Map that has some limitations.  

"In other words you are trying to say on row with 'Net Income' put row which is X, Y or Z, but those rows are not associated with 'Net Income' so you cannot."

I'm confused by this.  I was saying if it is net income, filter the transactions table to any objects that are tied to total revenue, total variable costs, etc and bring me the sum of that total.  I was able to get it with this formula.

if( [Map2] = 'Net Income', Sum( { $<[Map3] = {'Net Income'} > } [AT Trans Amount]/100), -Sum([AT Trans Amount]/100))

It's not the best practice, obviously.  I'm just trying to work on a short-term solution

[Lech_Miszkiewicz]

Hi @dyee4613 

I disagree little bit. You already have Map2 , Map3 or whatever else you use to create P&L. Just use this to remodel and create grouping dimensions. You already have some sort of map to identify everything you need for your P&L, so there is no extra effort needed just use those to create required mappings and groupings.

Regarding your set analysis question there is  difference in what you presented as the example:

if( [Map2] = 'Net Income', Sum( { $<[Map3] = {'Net Income'} > } [AT Trans Amount]/100), -Sum([AT Trans Amount]/100))

What above does is: for every row where[Map2] ='Net Income' use values where different field: [Map3] is also equal 'Net Income' else all rows.

You initial question however said:

If ( [Map2] = 'Net Income',

Sum( {$< [Map2] = {'Total Revenue', 'Total Variable Costs', 'Total Plant Overhead', 'Total Administrative Overhead'} > } [AT Trans Amount]),

Sum([AT Trans Amount])/100)

Which practically is not possile as you it is against association to have for [Map2] = 'Net Income' any other value then 'Net Income'. Your condition which says if [Map2] =... already checks only rows from red frame hence all other rows are are removed.

That is the core of it as in your example you are using the same field Map2, and the one you say is working you are using 2 different fields map2 and map3 and map3 can be just subset of map2.

Then there is a question of your data model and how those fields correspond to the rest of the model and whether or not you are using Map2 or Map3 as a dimension in your P&L.

Depending on your model there may be a ways of creating expressions which can do what you want but we would need littl enbit more context of how the model is built (screenshot of data model viewer) by using <TOTAL> in set analysis

[Qrishna]

if possible provide some sample data n expected output.