summing above and after in pivot table

[#1]

Hi there!

In a pivot table, I am trying to sum all the values above and after a certain point.  My dummy raw data looks like this:

resp	risk	freq
0.1	0.1	1
0.1	0.2	2
0.1	0.3	3
0.2	0.1	10
0.2	0.2	20
0.2	0.3	30
0.3	0.1	100
0.3	0.2	200
0.3	0.3	300

I would like my QV pivot table to give me this:

	resp
risk	0.1	0.2	0.3
0.1	111	110	100
0.2	333	330	300
0.3	666	660	600

where for example the "333" is the sum of all the freq's where risk <= 0.2 and resp >= 0.1.

I can get the first column (111,333,666) by using a straight table with only one dimention (e.g. risk) with this code:

rangesum(above(sum(freq),0,rowno()))

But it breaks when I bring in resp and change to a pivot and try and bring in "after" in addition to "above"

Can anyone show me how to get to what I need?  I know I am close, just missing a trick.  Thanks very much.

[johnw · Accepted solution]

In general, I try to avoid using above() and other positionally-dependent functions if I can.  They tend to fail if you sort your chart differently or make selections in the chart dimensions.

If performance allows, a simple and common solution is probably to define a data island for the dimensions.

Island:

LOAD num(fieldvalue('resp',recno())) as IslandResp
AUTOGENERATE fieldvaluecount('resp');
LEFT JOIN (Island)
LOAD num(fieldvalue('risk',recno())) as IslandRisk
AUTOGENERATE fieldvaluecount('risk');

dimension 1 = IslandResp
dimension 2 = IslandRisk
expression  = sum(if(risk<=IslandRisk and resp>=IslandResp,freq))

If that doesn't perform well enough, you can actually put the condition in your data model as linkage tables.  That's the solution I would probably personally use because I prefer data model solutions to chart solutions, and I prefer complexity in my script to complexity in my charts.  I'm not sure exactly what to call the new fields.  You'll probably want better names than I used.

RespLinkage:
LOAD num(fieldvalue('resp',recno())) as resp
AUTOGENERATE fieldvaluecount('resp');
LEFT JOIN (RespLinkage)
LOAD resp as resp2
RESIDENT RespLinkage;
INNER JOIN (RespLinkage)
LOAD *
RESIDENT RespLinkage
WHERE resp >= resp2;

Then repeat that structure for risk, and build your table like this:

dimension 1 = resp2
dimension 2 = risk2
expression  = sum(freq)

[#3]

Thanks, John - works perfectly!  Really, much appreciated!