Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
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.
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
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.
You are welcome & do post up further questions. I hope the research proves fruitful.