Partial sum on pivot table not showing grand total

[#1]

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

[whiteline]

Hi. You should provide more info about expressions that you use.

[#3]

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.

[whiteline]

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))

[#5]

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

[whiteline]

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().