Help on correct calculation

[#1]

Hello all,

I'm having some problems either in my calculation or my datamodel. I have no idea where I made my mistake.

In the attached example I've put in one line to demonstrate my problem.

I have a product which has a start and end date. The salesprice of this product was 60000 when sold. Every product has a 60months lifetime cycle. That means that for 60 months long each month this product has a value of 1000. But when I let my table make the subtotal, than it will say 1000 while it should be 12000.

The table is displaying everything correct. But it seems that it just doesn't want to make the correct calculation in my subtotals. Can anyone help me out with this one?

[swuehl · Accepted solution]

The total mode in a pivot table is expression total, you can't set it to sum of rows in expression tab like for a straight table chart.

But you can reproduce a sum-of-rows using advanced aggregation (i.e. the aggr() function). There is a chapter in the HELP that describes this (sum-of-rows in a pivot using advanced aggregation).

Your expression then looks like:

=sum(aggr(sum(Price)/60,Year,Month,Key))

You might also be able to use something like

=sum(Price/60)*count(distinct Month)

in your special case where all lines are the same amount.

Hope this helps,

Stefan

[#3]

Many thanks!

I just tried it and it worked. Just what I needed. I've been braking my head over this all weekend.

Appreciate the help Stefan