Hierarchy Mapping

[dyee4613]

Hello.  My company is planning on shifting it's reporting to QLIK and I'm trying to figure out the best way to utilize the P&L Visualization tool to recreate most of the financial reports.  I wanted to check with you guys regarding the best practice for leveraging this tool.  Please note - the financial rollups are not in our ERP so they will have to be recreated.  

Measure #1 - GL Mapping

The GL Mapping will just be a long series of IF statements outlining what accounts roll up into what bucket.  

Example: Depreciation is account 7000:7020 and NI is 3999 to 9999 so I'd write a formula like

If([GL] > 6999 and [GL] < 7021,'Depreciation',

If([GL] > 3998 and [GL] < 10000,'Net Income',

etc etc

This would be the first layer.  However, we have a lot of subheaders so I'd add in a second measure to add in the rollup accounts.

Measure #2 - GL Rollup

If([GL Mapping] = 'Net Income' or [GL Mapping] = 'Depreciation', 'Net Cash From Ops',

If([GL] > 6999 and [GL] < 7021,'Depreciation',

If([GL] > 3998 and [GL] < 10000,'Net Income',

etc..

This should get me all the accounts.  I will probably need a few more measures to order this.  

Measure #3 - Sum Logic

I would rewrite the last measure but this time I'm focusing on the SUM function, 

If([GL Rollup] = 'Net Cash From Ops', SUM(...)

This seems like it would work.  However, this doesn't seem like the best way to do this.  Additionally, I could see it being confusing to anyone not named me.  I wanted to check with you guys to see if you had any thoughts of a more efficient process.

[dyee4613]

Update - I created the calculated dimension.  I was planning on creating a measure using the calculated dimension and a set analysis.  However, I don't see the ability to reference my original calculated dimension.  

I think I was making this too complicated, though.  I'm going to upload a table with the fields I want in excel then I'll just create measure using set analysis or whatever it's called to create the line level logic.  

[marcus_sommer]

I suggest to create an appropriate dimension-table which includes all needed layer and intermediate states  and/or overlapping parts.

Such table might be created with n mappings and/or loops, like:

t: load * inline [
GL from, GL to, Desc
6999, 7021, Depreciation
7021, 9999, xyz
] while [GL from] + iterno() - 1 <= [GL to];

Even an Excel table with a lot of simple entries and copy & paste stuff would be possible and faster and more expedient as hard-coding all matching within n nested if-loops.
 

[dyee4613]

I had the same idea and I think it's working.  Thank you @marcus_sommer.

I have two more questions:

Question #1: SOLVED

Question #2: 

How can I make the value field larger?  I have to hover over to see it.

Question #3: While doing my hierarchy, it's too difficult to write down each line of code.  I'm going to want to switch it to variables.  I tried to create a variable and input it into my set analysis but I failed.  Can someone help me out?  

I want to create a variable called vOutsideSales which represents the following set analysis code.

Sum({$< Object = {">=4020<=4025",">=4160<=4168",">=4320<=4321","4380","4436"},[BU Category Code 21 - Code]= {"2000"}>}[Trans Amount]),

Also, please tell me the syntax using the variable within the set analysis.  Is it 

Sum({$< vOutsideSales>}[Trans Amount])

?

[marcus_sommer]

Normally the objects are adjusting itself in regard to the available screen. Maybe any setting of this object is wrong. You may look through the settings or just creating a new one.

Your hierarchy isn't really suitable for a P&L, yet. The aim should be to avoid all hard-coded stuff within the script as well as within the UI which means that the UI expression just looked like: sum(FIELD) and all logic is transferred to the dimensional layers.