topic Re: issue with rangesum(above(Total sum( in QlikView

issue with rangesum(above(Total sum(

Thu, 07 Feb 2013 16:22:13 GMT

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!!

Re: issue with rangesum(above(Total sum(

Gysbert_Wassenaar — Thu, 07 Feb 2013 19:22:26 GMT

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.

Re: issue with rangesum(above(Total sum(

Thu, 07 Feb 2013 19:43:34 GMT

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

All are numerical values.

Re: issue with rangesum(above(Total sum(

Thu, 07 Feb 2013 20:33:13 GMT

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

Re: issue with rangesum(above(Total sum(

Gysbert_Wassenaar — Thu, 07 Feb 2013 20:44:28 GMT

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.

Re: issue with rangesum(above(Total sum(

Thu, 07 Feb 2013 20:53:05 GMT

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!!

Re: issue with rangesum(above(Total sum(

Gysbert_Wassenaar — Thu, 07 Feb 2013 21:07:38 GMT

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.

Re: issue with rangesum(above(Total sum(

Thu, 07 Feb 2013 21:11:15 GMT

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!!

Re: issue with rangesum(above(Total sum(

Thu, 07 Feb 2013 21:47:44 GMT

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?

Re: issue with rangesum(above(Total sum(

Fri, 08 Feb 2013 06:18:57 GMT

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!

Re: issue with rangesum(above(Total sum(

Fri, 08 Feb 2013 08:39:49 GMT

right I think its almost there by doing this method:

if(GetCurrentField([Cycle 1])='SmokingHistory',
//$(=lineval)
//LineValue

rangesum(above(Total sum({$ < SmokingHistory={$(=lineval)} >} Event),0,rowno(total))),
)

The variable brings back the correct value and is essentially the same as just using LineValue function. For some reason the variable doesnt work in set analysis though, any ideas?

lineval variable contains:

FirstSortedValue($(=getcurrentfield([Cycle 1])), recID)

The idea is then I only need to do nested if for each dimension within [Cycle 1]

Re: issue with rangesum(above(Total sum(

Lee_Matthews — Mon, 07 Oct 2013 01:30:53 GMT

Here is an example of how to create the Kaplan Meier Curve

http://community.qlik.com/message/401280#