MTD Calculation by Site

[Anonymous]

In a pivot table, I have the following columns:-

1. Day

2. Weekday

3. Site

4. Daily Actual

5. MTD Actual

For example,

Day WeekDay Site Daily Actual MTD Actual

1 Tue A 100 100

1 Tue B 200 200

2 Wed A 250 350

2 Wed B 275 475

I would like to know how to write the expression for [MTD Actual] group by Site so that I have the results (MTD Actual) as shown above. I have tried to use the following formula :-

=

RangeSum ([Daily Actual], Above([MTD Actual]))

But the result is that it will give me the [MTD Actual] group by day instead of Site as shown below:-

Day WeekDay Site Daily Actual MTD Actual

1 Tue A 100 100

1 Tue B 200 300

2 Wed A 250 250

2 Wed B 275 525

Any idea how to fix this problem?

[jonathandienst]

Hi

I think you need something like

RangeSum ([Daily Actual], Above(Total <Site> [MTD Actual]))

Hope that helps

Jonathan

[Anonymous]

I tried to use

=

RangeSum ([Daily Actual], Above(Total <Site> [MTD Actual]))

in the expression, but it seems that the syntax is incorrect because I see the Error in Expression in the top left corner and it did not return any data. If I remove the <Site>, then it will result some values, but it will add all above disregarding the Site.

[jonathandienst]

Hi

You are right, Above does not support field qualifiers for the Total clause... Without knowing your data model in detail, its hard to say, but I suggest that you try something like:

=RangeSum([Daily Actual], Aggr(Above(Total [MTD Actual]), Site))

If you cant get that to work (maybe without the Total, I am not sure), then I suggest that you post your model, or a sample of your model so that I can look at it in some more detail.

Jonathan

[Anonymous]

Jonathan,

I have a model ready for you to test. How can I send it over to you?

Simon

[jonathandienst]

Hi

Attach it to your posting. Click the Options tag at the top of the post entry form and click the Add/Update button under file attachment.

Jonathan

[Anonymous]

Jonathan,

Thanks. I have found the formula to do it.

rangesum([Daily Actual],above(total [MTD Actual],2))