issue with rangesum(above(Total sum(

[#1]

Hi there,

I am trying to create a Kaplan Meier chart using two cyclical dimensions:

1) Time

2) Cycle 1 (containing SmokerHistory and differentation.

One of the expressions i need to calculate this is the cummulative number of deaths for each line on the graph. I can get it to work with one line (selecting a value for SmokerHistory for example) but it wont work for multiple lines as the cummulative deaths number does not seem to work.

My calculation for deaths looks like this:

rangesum(above(Total sum(Event),0,Time))

Event is 1 if there is a death.

I can get this to work in a pivot table but I need to display this on a line graph. How to I calculate the cummulative deaths for the line when there are multiple lines selected?

Please help!!

[Gysbert_Wassenaar]

You have no Time field or variable, so I don't understand why you put it in rangesum(above(Total sum(Event),0,Time)).

Anyway, I don't see what's wrong with the cumulative counts. They look correct to me.

[#3]

Time is a Cycle Group containing either Day, 7 Day interval, 30 day interval.

All are numerical values.

[#4]

Maybe I didn't make my self clear. Please see the file again but with a more clear view of the problem.

In order to get the graph lines to be accurate the correct output for cummulative deaths should look like this:

YearGroup	SmokingHistory	Patient Count	Cummulative Deaths Wrong	Cummulative Deaths Should be
		455	0
1	Life-long never	111	14	14
1	Not known	344	90	76
2	Life-long never	111	15	29
2	Not known	344	64	125
3	Life-long never	111	6	35
3	Not known	344	19	138
4	Life-long never	111	1	36
4	Not known	344	8	145
5	Life-long never	111	1	37
5	Not known	344	3	147
6	Life-long never	111	0	37
6	Not known	344	0	147

Also tried rangesum(above(Total sum(Event),0,$(=getcurrentfield(Time))))

This just seems to be a messy way of getting the same numbers as

[Gysbert_Wassenaar]

Sorry, but I just don't see it...

As far as I can tell your pivot table shows the same numbers as the line chart and the same as the numbers you posted above.

[#6]

if you open Demo Publicv2.qvw and choose YearGroup and Smoking History in the cycle views. This should show you the error. Thank you so much for helping!!

[Gysbert_Wassenaar]

Ah, you made a straight chart from it. Yeah, the above function can't skip rows so it won't calculate correct numbers then. If you change it to a pivot table and drag the Cycle1 group to above the expressions (as in the image in my previous post) then the numbers add up ok. If you want the cumulative numbers in a straight chart you'll probably need to precalculate them in the script.

Well, you could try something like this:

if(SmokingHistory='Life-long never',

rangesum(above(Total sum({<SmokingHistory={'Life-long never'}>}Event),0,rowno(total))),

rangesum(above(Total sum({<SmokingHistory={'Not known'}>}Event),0,rowno(total))))

But that's hardcoded and doesn't take into account the active field in the group, nor other SmokingHistory values than the two I've accounted for. Maybe that can be improved on using the concat, pick, match and GetCurrentField functions.

I think I have something workable. See attached qvw. I create some expressions in the script and stuff them in two variables. Using a variable in the set analysis expression doesn't work because the sets are calculated once for the entire chart, not per row. That's also why I had to resort to creating expressions with hardcoded values for the two fields from the Cycle 1 group.

[#8]

Yeh I can get the numbers in a Pivot table but I need to display them in a line graph, any ideas on how i could precalculate this in a script without loosing the functionality?

Thanks again!!

[#9]

Thank you again for your help, i think that may be to involved for an expression and would like to push it back into the load script if possible any ideas on how I might approach that?

[#10]

if(SmokingHistory='Life-long never',

rangesum(above(Total sum({<SmokingHistory={'Life-long never'}>}Event),0,rowno(total))),

rangesum(above(Total sum({<SmokingHistory={'Not known'}>}Event),0,rowno(total))))

This seems to work, is there a way i can use set the value of the set analysis search value to what ever the value of SmokingHistory is on each line?

Thanks again very very helpful!