topic Re: Identifying & applying expressions to each level of a hierarchy in QlikView

Identifying & applying expressions to each level of a hierarchy

kevinstanfield — Fri, 24 Jul 2015 08:20:54 GMT

Hi,

I’ve been trying to figure out a way of adding some logic that allows 3 separate straight tables to be produced each showing what is effectively a level of a hierarchy based on Group name (level 1), Corp Name (level 2) & Account(level 3).

This is probably easier to explain using an example.

Group Name Corp Name Account Margin

ABC ABC1 1234567 £100

ABC ABC2 2345678 £200

CBA 7654321 £75

CBA 8765432 £75

9876543 £10

So the first table would sum margin at Group Name level =£300

I want the next table to be populated only for customers where in the hierarchy there is no Group Name i.e. CBA sum margin= £150 for them and lastly a 3^rd table to be populated with customers who have no Group Name and no Corp Name i.e. their highest level in the hierarchy is Account 9876543 sum margin =£10

I’m sure there must be an easy way of doing this but after numerous hours banging my head against the wall (& searching qlik community/you tube) I’ve not got it.

I want all 3 tables to show all the time so this is not a conditional layout issue.

Please note that I’m self-taught,non-techie QlikView user so if possible keep those answers simple.

Many thanks in anticipation.

Kevin

Re: Identifying & applying expressions to each level of a hierarchy

jfkinspari — Fri, 24 Jul 2015 08:44:29 GMT

Hi Kevin,

Sure, that shouldn't be too hard to accomplish.

If you use a Straight table, and we are going to make the first table at Group level, then include Group Name and Account as dimensions. For the Group Name check 'Suppres when value is Null'. The expression is standard, e.g. sum(Margin).

If you don't want to display the Group Name, then hide the column from the Presentation tab.

Re: Identifying & applying expressions to each level of a hierarchy

kevinstanfield — Fri, 24 Jul 2015 08:47:58 GMT

Thanks Jens - I'll have a go later and let you know how I get on.

Re: Identifying & applying expressions to each level of a hierarchy

jonathandienst — Fri, 24 Jul 2015 08:54:40 GMT

Use the following calculated dimensions for the 3 tables:

Group Name:

=Aggr(If(Len([Group Name]) > 0, [Group Name]), [Group Name])

Corp Name:

=Aggr(If(Len([Group Name]) = 0 And Len([Corp Name]) > 0, [Corp Name]), [Corp Name])

Account

=Aggr(If(Len([Group Name]) = 0 And Len([Corp Name]) = 0, [Account]), [Account])

Check suppress nulls on Group Name for the first and Corp Name for the second.

Re: Identifying & applying expressions to each level of a hierarchy

kevinstanfield — Fri, 24 Jul 2015 09:01:12 GMT

Thanks Jonathan. I'll let you know how I get on.