topic Re: Force sum on higher level in App Development

Force sum on higher level

Anonymous — Wed, 01 Mar 2017 16:23:57 GMT

Hello,

I would like to force a sum on a certain level independant of the selections in a lower level.

Example:

Group	Detail	Value	Value forced
A	A-1	2	28
A	A-2	3	28
A	A-3	5	28
A	A-4	7	28
A	A-5	10	28
A	A-6	1	28

I can show the forced value for group A via the TOTAL function : sum(TOTAL<Group> value).

But if I do a selection within the group (select A-1 and A-2 for example), I would like to keep the 28, with the above formule it wil give the total of the selection (see below).

Group	Detail	Value	Value forced	Value with current function
A	A-1	2	28	5
A	A-2	3	28	5

In a dashboard i'd like to show the total value of A even if i only selected A-1

Thanks in advance for the help.

Jonah

Re: Force sum on higher level

sunny_talwar — Wed, 01 Mar 2017 17:14:22 GMT

Try this:

Sum(TOTAL<Group> {<Detail>} value) * Avg(1)

Re: Force sum on higher level

Saravanan_Desingh — Thu, 02 Mar 2017 01:40:06 GMT

You can try:

Sum({1}TOTAL Value)

Re: Force sum on higher level

Anonymous — Thu, 02 Mar 2017 08:07:41 GMT

Hello,

Thanks Sunny for the answer, this partly gives me a solution but also creates another problem

(@ Saran, your solution only applies if A is the only group, which isn't the case.)

Group	Detail	Value	Value Group
A	A-1	2	10
A	A-2	3	10
A	A-3	5	10
B	B-1	7	18
B	B-2	10	18
B	B-3	1	18

When only selecting A-1, I would like to only see the blue line below, is there any way to obtain the view?

But with your formula I also get A-2 and A-3 as well as B-1, B-2 and B-3 with null values (i.e. it shows all groups without the Value but still shows them in the table).

A	A-1	2	10
Group	Detail	Value	Value Group
A	A-2	0	-
A	A-3	0	-
B	B-1	0	-
B	B-2	0	-
B	B-3	0	-

Thanks in advance

Jonah

Re: Force sum on higher level

sunny_talwar — Thu, 02 Mar 2017 16:20:13 GMT

Have you unchecked 'Show Zero Values' or is this checked? Uncheck it for me and see if that solves your issue

Re: Force sum on higher level

Anonymous — Thu, 02 Mar 2017 16:51:15 GMT

As a (happy) new Qlik User I'm still learning the basics the "Show zero values" resolved my issue, thanks a lot!

To improve my understanding of the tool Tool I have an additional question : What's the function of the *Avg(1) at the end of your proposed formula?

If I remove the "*Avg(1)" of the formula and uncheck the "Show zero values", I still have the above problem.

Re: Force sum on higher level

sunny_talwar — Thu, 02 Mar 2017 17:21:27 GMT

Since we are ignoring selection in Details field, QlikView will continue to show all rows when you select a single value under details. But to force them to be 0 we multiply it by Avg(1). Avg(1) will be equal to 1 for your selections and will equal to 0 out of your selections.