topic Re: Overlapping sums and hierarchy in App Development

Overlapping sums and hierarchy

John321 — Tue, 02 Mar 2021 09:26:06 GMT

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

Re: Overlapping sums and hierarchy

rbartley — Tue, 02 Mar 2021 10:03:54 GMT

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?

Re: Overlapping sums and hierarchy

John321 — Tue, 02 Mar 2021 10:16:34 GMT

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?

Re: Overlapping sums and hierarchy

rbartley — Wed, 03 Mar 2021 16:58:55 GMT

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.

Re: Overlapping sums and hierarchy

John321 — Thu, 04 Mar 2021 16:01:31 GMT

Hello,

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