topic Sum of Two Totals Fields in App Development

Sum of Two Totals Fields

theboss-123 — Tue, 24 May 2022 14:05:57 GMT

Hi is there a way to calculate sum between two TOTALS in PIVOT table or Straight Table ?

for example i have the table bellow

	A	B	C
1	12	14	0	"B+C/E"
2	14	12	30
3	20	24	40
4	20	0	50
		TOTAL1	TOTAL2
			E=TOTAL1+TOTAL2

I want to calculate TOTAL 1 + TOTAL2

and also B+C/E

E is TOTAL1 and TOTAL2

thanks for ur help

Re: Sum of Two Totals Fields

marcus_sommer — Wed, 25 May 2022 06:26:01 GMT

In general it's possible to ignore all respectively a certain dimensionality and/or applying further conditions within an expression, maybe something in this way:

sum({< HorizontalDim = {'B', 'C'}>} total <VerticalDim> Value)

Important is that there is a place where the calculations could be displayed - means there must any partial-sum and/or dimension-value where it could be done. By the partial-sums they could be queried with dimensionality() and/or secondarydimensionality() and you may add an (artificial) extra dimension if you need more than one and/or you need to add extra dimension-values to your (doubled) fields.

This means it's possible but it may not be trivial and may need some efforts and could have some side-effects. Therefore it might be easier to integrate such requirements directly into the data-model maybe by (pre-)calculating the results already in the script or making there an appropriate matching of the data, for example with an as-of-table.

- Marcus

Re: Sum of Two Totals Fields

theboss-123 — Wed, 25 May 2022 09:05:36 GMT

Hi Marcus Firstly thanks for your response my pivot table looks like

can you explain me with exemple pleasebecause im a beguinner in QLIKVIEW

Re: Sum of Two Totals Fields

theboss-123 — Wed, 25 May 2022 09:31:37 GMT

here is the synthaxe of the formula of Set but it is incorrect

sum({< [MOULE FRAIS] = {'60/65'}>} total <Provenance> {'Espagne'})

but it is not working

Re: Sum of Two Totals Fields

marcus_sommer — Wed, 25 May 2022 10:04:18 GMT

The syntax is wrong and it should rather look like:

sum({< [MOULE FRAIS] = {'60/65'}>} total <Provenance> [Value])

whereby your pivot and your tried expression looked quite different to your example from the start.

It's not quite clear for me what you are trying to do. I assume the aim is calculating the quote of the amounts and sales per the dimension-levels. If so it could be done with the mentioned total - means just using your origin expression + an appropriate total-statement.

If some of the dimension-values should be added together and/or various conditions should be applied and/or it should be displayed as extra values it could become quite complex. At this point it should be considered to show only these values within the wanted granularity and applying on it the quote-calculation instead of trying to show the data + granularity of A but calculating with the data + granularity of B. I don't want to say that this won't be technically possible but it's most likely not sensible in regard to the development efforts and the usability.

- Marcus

Re: Sum of Two Totals Fields

theboss-123 — Wed, 25 May 2022 12:03:43 GMT

thanks sum({< [MOULE FRAIS] = {'60/65'}>} total <Provenance> [Value])

but what do you mean by [Value]

and in my case

I want to calculate for example "64 + 120" which are respectively the Nbr Futs of Espagne and MAROC

Re: Sum of Two Totals Fields

marcus_sommer — Wed, 25 May 2022 12:25:34 GMT

Value is just a placeholder for the field which should be aggregated. To get the sum of 64 + 120 you may apply:

sum(total <Month> [Nbr Futs])

- Marcus

Re: Sum of Two Totals Fields

theboss-123 — Wed, 25 May 2022 13:21:38 GMT

Hi Marcus

that solved 50% of the issues and then i want to calculate for example

(0+45)/sum(total <Month> [Nbr Futs]) and that shall aplly on all line with yellow colors

Re: Sum of Two Totals Fields

marcus_sommer — Wed, 25 May 2022 13:38:21 GMT

Try it with:

sum(total <Month, [MOULE FRAIS]> [Nbr Futs]) / sum(total <Month> [Nbr Futs])

- Marcus

Re: Sum of Two Totals Fields

theboss-123 — Wed, 25 May 2022 13:53:09 GMT

You are Genius thanks Marcus