Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)
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)
Thanks, John - works perfectly! Really, much appreciated!