Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am trying to show subtotals on a pivot table I have and the subtotal's appear to work however the grand total is coming to zero. I'm not quite sure what or where I am going wrong.
Can anyone shed any light please?
Many thanks in advance.
C
Country | Stock Class | Stock on Hand | Allocated Stock | Stock On Order | Stock Balance |
---|---|---|---|---|---|
UK | CARR | 293.87 | 42.36 | 0.00 | 251.51 |
FITTINGS | 637108.42 | 8357.22 | 20602.59 | 649353.79 | |
HOSE | 734902.11 | 7691.85 | 7086.11 | 734296.37 | |
OVERHEAD CHARGE | 15507.89 | 0.00 | 4898.58 | 20406.47 | |
SUNDRY | 0.00 | 0.00 | 0.00 | 0.00 | |
0 | |||||
Total | 1387812.29 | 16019.79 | 32515.64 | 1404308.14 | |
Germany | FITTINGS | 314274.48 | 348.65 | 5633.55 | 319559.39 |
HOSE | 147646.84 | 180.01 | 8250.71 | 155717.55 | |
OVERHEAD CHARGE | 0.00 | 0.00 | 0.00 | 0.00 | |
0 | 0.00 | 0.00 | 0.00 | 0.00 | |
Total | 461921.33 | 528.65 | 13884.26 | 475276.94 | |
0 | 0 | ||||
0 | |||||
Total | |||||
Total |
Hi. You should provide more info about expressions that you use.
If I take just one of the columns in question my expression is
If(Currency='£ GBP' and MT.CountryID='Germany', sum({<Cal_FullDate = {"<=$(=max(Cal_FullDate))"}, Cal_Year, Cal_Month>} QuantityValue)*VarExRate,
If(Currency='£ GBP' and MT.CountryID='UK', sum({<Cal_FullDate = {"<=$(=max(Cal_FullDate))"}, Cal_Year, Cal_Month>} QuantityValue),
If(Currency='€ EUR' and MT.CountryID='Germany', sum({<Cal_FullDate = {"<=$(=max(Cal_FullDate))"}, Cal_Year, Cal_Month>} QuantityValue),
If(Currency='€ EUR' and MT.CountryID='UK', sum({<Cal_FullDate = {"<=$(=max(Cal_FullDate))"}, Cal_Year, Cal_Month>} QuantityValue)/VarExRate,
))))
In short it calculates the monetary value of stock to date in either GBP or Euos and depending on whether a row of data is from Germany or UK and the currency variable selected.
So it can't be calculated for total level this way.
For example instead of particular value of Currency and MT.CountryID fields you have all Currency and MT.CountryID values an so on.
You have to calculate it with aggr() function for each dimension manually and then use sum() to aggregate:
=Sum(aggr(...your expression..., Currency, MT.CountryID))
Hi whiteline,
Thanks for this.
I guess I've stayed away from the aggr() stuff becasue I've not fully understood it and up until now I've been ok; I can't avoid it anymore - Do you know where I could get a "layman's" explaination of it and how to use it? Hopefully I'll be able to go from there.
Thanks again for you help.
C
Start from help 😃
Imagine it as some kind of invisible straight table with corresponding dimensions and one expression.
And then you use Sum/Count or another aggregation function over the values in that table.
It's rather crude analogy.
The same thing when calculated dimensions are created using aggr().