Line diagram: change dates to date intervals?

[daumkep]

Hi,

I have a line diagramm with dates on the x-axis and visual acuity measurements of a patient on the y axis. I want to calculate the visus change per year. In the example below, thse visus change is ~ 0.1 per year. But linest_m gives me 0.00030572. So I would somehow want to work with relative dates starting the minimum date as day zero. 

Could somebody tell me  howto do this?

Thanks a lot and all the best
Philipp

[chrismarlow · Accepted solution]

Hi,

I think what you are seeing is because as far as the equation is concerned each day is =1 on the x-axis. So you can rebase using a calculated dimension to make each year =1, so I built a toy model with just date & a value & the following looks like it works;

= (DayNumberOfYear(Date)/DayNumberOfYear(YearEnd(Date)))+(Year(Date)-Year(YearStart(Min(TOTAL Date))))

The bit counting the days in the year is probably overkill, /365 would be pretty accurate I guess. Result below with chart on left using the date and right the calculated dimension;

Hope that helps.

Cheers,

Chris.

[daumkep]

Hi Chris,

that's exactly what I was looking for, thanks a lot for your help!!!

One more question, let's assume I have more than one patient in my data, and I want to show the yearly visus change (in your example e.g. 0.314111) grouped by patient, in order to find out the patients with the highest visus gain pear year. The following formula gives me the visus change of one patient:
=LINEST_M(RightVisus,(DayNumberOfYear(Date)/365)+(Year(Date)-Year(YearStart(Min(TOTAL Date)))))

Do you or somebody else know how to put this value into a list box grouped by patient?

In the screen I show how it currently looks like. The calculation of gradient M works well if I select one patient. What I try now is to have a list box which shows the gradients grouped by patient.

The second image shows my starting point, a listbox where I entered the formula. It now needs to be grouped py patient, I guess, but I don't know how.

Thanks a lot for any help.
Philipp

[chrismarlow · Accepted solution]

Hi Philipp,

You are welcome, my wife is a dispensing optician so would be impressed that I am doing something practically useful for a change.

I think you are nearly there. Use a straight table with patient as dimension and the expression you propose & I think it works (I've hardcoded the sets in the charts to show what QV calculates for each one);

It looks like it works without having to modify the bit that finds the earliest date when there are different start dates by patient, but you might want to check some data by hand as I was expecting to have to change it to cope with different ranges of dates (if that is a concern in your data set).

Cheers,

Chris.

[daumkep]

Hi Chris,

that worked, thanks a lot.

You can tell your wife that you now work in German project that tries to predict the therapy course of macular degeneration by injection of VEGF inhibitors. The visual acuity is extracted from doctors letters using text mining. The current problem is that the visus gain of most patients is rather a text mining artefact than a real visus gain (argh). That's why I started feeding the data into Qlikview to get a fast evaluation option of the data and the extracts. As I'm a Qlikview newbie, you really helped me a lot!

All the best
Philipp

[chrismarlow]

You are welcome & do post up further questions. I hope the research proves fruitful.