Sum Row in Pivot Table

[derekjones]

Hi All

Hoping this is an easy answer, but for me the solution is eluding me and any related posts don't seem to answer my exact issue.

Here's a simplified example of my issue...

I have a daily GP Target for each rep, if they don't hit their GP target for the day, they get no points, if they hit, they get 1 point and if they get over 150% of target for the day they get a further point (i.e. 2 Points for the day):

GP/Target < 100% = 0 Points

GP/Target < 150% = 1 Point

GP/Target > 150% = 2 Points

I want to be able to show for each rep, their daily stats for the month plus a total of how many points they have accumulated in the month. I've tried using a pivot table with dimensions of Rep and Date with Expressions for Target, GP and Points, but the Row Total for Points is calculating over the whole month not totalling the days. See example below, Total Points should be 4 as 2 days hit over 150%, but the total is actually calculating over the total.

I've also attached a simplified QVW of my problem.

Any suggestions welcome.

My thanks in advance

Derek

[#2]

Try with this expression:

=

sum(aggr(IF([Achieved]<1,0,IF([Achieved]<1.5,1,2)),SHIPPED_DATE))

[rbecher · Accepted solution]

Hi Derek,

this should work:

=sum(aggr(if(sum({<Month={"0"}>}GP) / (Sum({<Month={"0"}>}Target)/20) < 1, 0, if(sum({<Month={"0"}>}GP) / (Sum({<Month={"0"}>}Target)/20) < 1.5, 1, 2)), RepName, SHIPPED_DATE))

I think you cannot use [expressions] for that.

- Ralf

[derekjones]

Hi emaC

Nice try, but no cigar I'm afraid.

The result is 2 points in every date (even if not hit) plus it's not restricting the dates to this month.

Thanks all the same for giving it a try.

[rbecher]

My suggestion works correct, it counts 7!

- Ralf

[derekjones]

Hi Ralf

That worked thanks.

Now to translate the example back to my actual data!

Thanks again, I owe the forum a drink! 🙂