Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Try it with:
sum(total <Month, [MOULE FRAIS]> [Nbr Futs]) / sum(total <Month> [Nbr Futs])
- Marcus
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
Hi Marcus Firstly thanks for your response my pivot table looks like
can you explain me with exemple pleasebecause im a beguinner in QLIKVIEW
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
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
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
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
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
Try it with:
sum(total <Month, [MOULE FRAIS]> [Nbr Futs]) / sum(total <Month> [Nbr Futs])
- Marcus
You are Genius thanks Marcus