Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 3rd 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
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.
Thanks Jens - I'll have a go later and let you know how I get on.
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.
Thanks Jonathan. I'll let you know how I get on.
KS