pivot table totals not correct

[#1]

hi.

i have the following issue.

the pivot table totals are not correct;

is there possibly a way to use  the aggr function to solve this`

i have attached the document as well for more insight into the problem.

Message was edited by: Bradley Coyne

[swuehl]

Bradley,

when you are saying 'not correct', you expect a sum of rows as total, right?

A pivot table will always calculate the expression total on total line's granularity, but you can indeed use advanced aggregation (aggr() function) to calculate a sum of rows total in the pivot table.

There is a chapter in the HELP that explains that in more detail, but basically, if your chart dimensions are Dim1, Dim2 and your chart expression is EXP, you need to use something like

=sum( aggr( EXP, Dim1, Dim2))

as modified expression.

In your case, dimensions are not pure fields, but calculated dimensions, which you can't use in aggr() function directly.

As a first step, you need to precalculate whereever possible all dimensions in your script, like

LOAD Itemnumber,

          left(Itemnumber,7) as ItemnumberShort,

          ...

for your first dimension.

[#3]

Hi Bradley,

it looks like problems with rounding of integers. I changed the format of the first and second expression to number(2) and the sums seem to be correct.

Hth
R

[stevedark]

Hi Bradley,

You are on the right track with Aggr, but I think that your calculated dimensions may be causing things to not work correctly.  I tried this for the first expression:

=sum(Aggr(

sum(if([OrderYearWeek] >=

weekyear(today()-(7 * vAntalVeckor))&num(week(today()-(7 * vAntalVeckor)),00)

,[Ordered qty],0))

/vAntalVeckor, Itemnumber, Colour, LagerArtikel, SvItemdesc, ItemstatusToday, LagerRisk))

The Aggr parameters being all fields referenced in the calculated dimensions.

I would strongly recommend trying to build all dimensions that you want to use in this pivot in the load script, eg:

left(Itemnumber,7) as [Item Prefix],

This should make the Aggr statement behave how you want it to.

Hope that helps,

Steve

[#5]

Hi Thank you for the replies i have now corrected the dimensions in the script and read the help guide on aggr.. but still recieve the wrong totals.

cannot understand why this is happening, any further suggestions.

i have updated the qvw file with the new one. in the message above.

Best

Brad

[swuehl]

Your expression seems ok to me (I am looking the first one, with the advanced aggregation only).

But on number tab, you set number format to integer, so you get a difference because of rounding / truncation between the sum of rows and the total line. Maybe include the rounding in your expression.

[stevedark · Accepted solution]

It would appear to require a combination of the Aggr and the Round, giving a first expression of:

=sum(Round(Aggr(

sum(if([OrderYearWeek] >=

weekyear(today()-(7 * vAntalVeckor))&num(week(today()-(7 * vAntalVeckor)),00)

,[Ordered qty],0))

/vAntalVeckor, Category, Colour, LagerArtikel_, SvItemdesc, [Lager/Risk]), 1))

Obviously, not rounding on each row will give a more accurate number on the totals - but it will look a bit strange when it doesn't add up.  Roland's suggestion of showing to a couple of decimal places would make sense.

Steve

[#8]

Hi all,

For my oppion this is a rounding stuff. You will almost have some odd sums when processing real numbers (with decimals) and showing results as integers. With results I mean two things: the expression itself and the total of the expression.

Pls see app, in which I tried to show a little example.

HtH

Roland

[stevedark]

Hi Roland,

This all depends on whether the intention is to show the total of all the rows above, or what the result would be were the calculation calculated across all data.

The latter is most likely the more useful and accurate, but the impression I got was that the intention was to have the numbers totalled.  Obviously this distinction is not important if the expression is only a sum in itself - but as a division is done in the expression it is key to understand what is required.

Perhaps Bradley could let us know on that front?

Either way, I agree rounding will be part of the solution - as your example shows very clearly.

[#10]

Hello.

Rolands example highlights the differents possibilties and this is really great for future use.

in my example i have the field Quantity which is carries numerous decimal places for example 10,0000.

this is by design in the erp system as i guess they could potentially have 10,5 in the case of semi produced products.

at the moment the case is that they dont use this field in this way and it is always a whole number i.e 10,00.

in the first three columns of my example the tricky part is that i work out averages over an amt of weeks, so for example the order avg over the 8 weeks. a likely example could be 30/8 = 3,75.

in this case it would be good to show 4 in the total column. However in the columns that follow there are no more avgs only complete totals, ie total amt sold for the last ten weeks.

i really appreciate all the help and have succeeded now in my task. i realise the power of QV and the aggr function and have taken a step forward in learning about round function in combination.

it feels really good that this community is so active and really keen on helping out.

thanks guys!

Best

Brad