Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hey everyone, good morning!
I have a pivot table that calculates volume deviations between two periods.
Ex: (Production of a 'x product')
Day | Morning (lb) | Afternoon (lb) | Desv. |
---|---|---|---|
18/05 | 1000 | 500 | -500 |
19/05 | 200 | 300 | 100 |
20/05 | 400 | 5000 | 4600 |
Where 'Day' is a dimension and Morning, Afternoon and desv are expressions.
All these expressions are conditionated to this condition: If(sum of morning) - sum(afternoon) is > 400 or <-400, expression (sum or m/a), else, 0.
After that I hide 0, so my table gets this way:
Day | Morning (lb) | Afternoon (lb) | Desv. |
---|---|---|---|
18/05 | 1000 | 500 | -500 |
20/5 | 400 | 5000 | 4600 |
However, when I insert the subtotal into the table, it remains calculating the whole table:
Day | Morning (lb) | Afternoon (lb) | Desv. |
---|---|---|---|
18/05 | 1000 | 500 | -500 |
20/05 | 400 | 5000 | 4600 |
Total | 4200 |
Do you guys know how to sum only the visibles values?
Kind regards, Luiz Bisco
Hi Luiz,
Yes. You need to use Aggr in order to get total value of visible rows. Like below
Sum(Aggr(If(sum ([Morning (lb)]) - sum([Afternoon (lb)])>400 or sum ([Morning (lb)]) - sum([Afternoon (lb)])< -400,Sum([Morning (lb)]),0),Day))
Add a sum first in Your Expression. Do not start With an if statment:
You could change your Desv calculation, to be the one you said,
If(sum of morning) - sum(afternoon) is > 400 or <-400, expression (sum or m/a), else, 0
so that your top chart (prior to conditional show) would have a 0 in the Desv column. This way your subtotal would be what you expect?
/Edit: Also, yes as Staffan says, its not good practice to start with the if, you should start with 'SUM' in the epression, so something like:
SUM( if( (sumOfMorning)-(sumOfAfternoon) >400
or (sumOfMorning)-(sumOfAfternoon) <-400,
(sumOfMorning - sumOfAfternoon),
0)
)
You need to calculate this on the day-level with an aggr-function, like:
sum(aggr(YourExpression, Day))
- Marcus
Hi Luiz,
Yes. You need to use Aggr in order to get total value of visible rows. Like below
Sum(Aggr(If(sum ([Morning (lb)]) - sum([Afternoon (lb)])>400 or sum ([Morning (lb)]) - sum([Afternoon (lb)])< -400,Sum([Morning (lb)]),0),Day))
But if I do that, how can show only the desviations that I want (only > 400 or <-400)?
Tamil, thank you!
It works, but now I have another problem. The total is correct for the summing, but i tried to insert a column with percent (and the conditions to calc remains). see the example:
Day | Morning (lb) | Afternoon (lb) | Desv. | Desv % |
---|---|---|---|---|
18/05 | 1000 | 500 | -500 | -50% |
20/05 | 400 | 5000 | 4600 | 1150% |
Total | 1400 | 5500 | 4100 | 1100% |
The desv %, should be 292,8% (4100/1400), but, its is giving me the sum of Desv %.
Do you know how to handle with it?
I'm already greatful,
Sincerely, Luiz Bisco
Hi Luiz,
I have attached a sample app. You can simply refer the column label like below,
[Desv.] / [Morning (lb)]