topic Sum Row in Pivot Table in QlikView

Sum Row in Pivot Table

derekjones — Tue, 22 Feb 2011 15:12:47 GMT

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

Sum Row in Pivot Table

Tue, 22 Feb 2011 15:27:18 GMT

Try with this expression:

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

Sum Row in Pivot Table

rbecher — Tue, 22 Feb 2011 15:43:53 GMT

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

Sum Row in Pivot Table

derekjones — Tue, 22 Feb 2011 15:44:55 GMT

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.

Sum Row in Pivot Table

rbecher — Tue, 22 Feb 2011 15:47:33 GMT

My suggestion works correct, it counts 7!

- Ralf

Sum Row in Pivot Table

derekjones — Tue, 22 Feb 2011 15:54:58 GMT

Hi Ralf

That worked thanks.

Now to translate the example back to my actual data!

Thanks again, I owe the forum a drink! 🙂