topic Hierarchy Mapping in QlikView

Hierarchy Mapping

dyee4613 — Tue, 28 May 2024 17:36:13 GMT

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.

Re: Hierarchy Mapping

dyee4613 — Tue, 28 May 2024 20:37:19 GMT

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.

Re: Hierarchy Mapping

marcus_sommer — Wed, 29 May 2024 12:07:38 GMT

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.

Re: Hierarchy Mapping

dyee4613 — Wed, 29 May 2024 18:02:43 GMT

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

Re: Hierarchy Mapping

marcus_sommer — Thu, 30 May 2024 12:58:24 GMT

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.