Overlapping sums and hierarchy

[John321]

Hello,

I'm pretty new to Qlik Sense and I'm having trouble solving this kind of problem.

So lets say here is the data...

Codename             sums

Code1                       10

Code2                       20

Code3                       30

Code4                       10

and I would need to transform it into this

Sum of Hierarchy1   30

      Code1                       10

      Code2                      20

Sum of Hierarchy2    70

    Code3                         30

     Code4                        10

So codes 1 and 2 belong to a certain hierarchy and so do codes 3 and 4. The thing is that the subtotal of codes 3 and 4 need to take the other subtotal into account.

I've run out of ideas how should I do this. Could you help me?

Thank you in advance

[rbartley]

So, are you saying that Code 1 and Code 2 belong to Hierarchy1 and Hierarchy 2, whereas Code3 and Code 4 only belong to Hierarchy 2? Also, does the data have to be displayed in a table exactly as you have shown with codes then sub-total?  It will be more complicated to show only the additional codes under Hierarchy 2 rather than alll of the codes.

Do you have an app you can post to save me time having to create one?

[John321]

Hi Rbartley,

Yes. So what I need to do is to have the Hierarchy1 total included in Hierarchy2 total. So all in all Hierarchy2 has all the codes included.

Unfortunately I don't have the possibility to send an app... This is due to my company.

I wonder if this is possible to do with parent child hierarchy or some sort of? 

[rbartley · Accepted solution]

The only way that I can think of achieving what you are asking for would be to edit your data model to create the hierarchy sub totals as though they were codes and then create a sort order field to achieve the correct ordering.  The problem for a standard pivot table is the fact that you have codes belonging to multiple hierarchies.

So, you would start with the initial data load, which gives you 

Codename             sums

Code1                       10

Code2                       20

Code3                       30

Code4                       10

Then create a further load statement with Code 'Sum Hierarchy 1' summing the sums field for the codes in hierarchy 1 concatenated with the first table, then another load statement with Code 'Sum Hierarchy 2' summing the sum field for the codes in hierarchy 2. You would also need to add a sort field with 'Sum Hierarchy 1' with value 1, Code 2 with sort value 2,...'Sum of Hierarchy 2' with value 4, etc.....

Take a look at the attached qvf file, but bear in mind that this has been designed to fit the specific case you mentioned above; you might need to adapt it further to meet your full requirement.

[John321]

Hello,

Thank you for providing this solution. It seems to tackle the problem 🙂