Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Partial sum on pivot table not showing grand total

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




5 Replies
whiteline
Not applicable

Re: Partial sum on pivot table not showing grand total

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

Not applicable

Re: Partial sum on pivot table not showing grand total

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
Not applicable

Re: Partial sum on pivot table not showing grand total

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

Not applicable

Re: Partial sum on pivot table not showing grand total

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
Not applicable

Re: Partial sum on pivot table not showing grand total

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