Identifying & applying expressions to each level of a hierarchy

[kevinstanfield]

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

[jfkinspari]

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.

[kevinstanfield]

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

[jonathandienst]

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.

[kevinstanfield]

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

KS