Qlik Community

Ask a Question

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor II
Contributor II

Line diagram: change dates to date intervals?

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

 

image.png

2 Solutions

Accepted Solutions
Specialist
Specialist

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;

20190209_1.png

Hope that helps.

Cheers,

Chris.

View solution in original post

Specialist
Specialist

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);

20190210_1.png

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.

View solution in original post

5 Replies
Specialist
Specialist

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;

20190209_1.png

Hope that helps.

Cheers,

Chris.

View solution in original post

Contributor II
Contributor II

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


image.pngimage.png

Specialist
Specialist

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);

20190210_1.png

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.

View solution in original post

Contributor II
Contributor II

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

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