topic max date about n lines (rows) in QlikView

max date about n lines (rows)

Mon, 12 Dec 2011 14:49:55 GMT

We have a table with information on projects, tasks, and other indicators. But this table does not contain current information, if it is a historic guard. Imagine a project with n tasks, these tasks (VS_TAREA_ID) change over time (FECHA_MOD_TAREA) (change your VS_CONSUMIDO_HORAS, for example) ... because these transactions / changes, are stored in the database.

Well, at this point, what we get is for a date selected by you (stuck on a calendar, for example), tasks with minor modification dates selected date, but only the immediately preceding (1 row for each project-task-date).

The task A, belonging to the project Pro_A has changed 4 times. Was changed at date 1, 5, 10 and 20. Then, in the source table, we will have 4 records in which transactions are recorded these 4 ... each row will have, in the "VS_Consumido_horas."

proyecto	tarea	Fecha_modificación	Consumido
Pro_A	A	01/12/2011	45
Pro_A	A	5/12/2011	56
Pro_A	A	10/12/2011	67
Pro_A	A	20/12/2011	100

Putting a filter (manually by the user displays the schedule) and enter 11 / / 12 / 2011 ... to get dates prior to the date 11/12/2011:

proyecto	tarea	Fecha_modificación	Consumido
Pro_A	A	1/12/2011	45
Pro_A	A	5/12/2011	56
Pro_A	A	10/12/2011	67

So far, so OK . But The problem is: we want only the immediately preceding (the top of the filter resulting from the first), ie, the date 10/12/2011 , for each task-date.

proyecto	tarea	Fecha_modificación	Consumido
Pro_A	A	10/12/2011	67

Thank you for all!

max date about n lines (rows)

Mon, 12 Dec 2011 14:57:08 GMT

Look into using Group By in the script.

Should be able to Group By proyecto,tarea with a Max(Fecha_modificación)

and then link the table again by the date to the Max Date

max date about n lines (rows)

Mon, 12 Dec 2011 15:37:45 GMT

The problem is that i want to have avaiable all lines (all history task) before applicatting the filter... (if i apply group by, it loses the history of task.

The steps are:

1. I have the whole set of tasks and their history.
2. Filter by date and for that date, extract ll tasks with earlier modification date.
3. of that subset, only extract the maximum date.

That is, the deadline less than the date entered in the filter

Re: max date about n lines (rows)

Mon, 12 Dec 2011 16:21:04 GMT

if I understood correctly, this is what you need:

=Date(FieldValue('Fecha_modificación',FieldIndex('Fecha_modificación','$(=Fecha_modificación)')-1))

Re: max date about n lines (rows)

llauses243 — Tue, 13 Dec 2011 02:23:46 GMT

Hi,

This is my offer, pls to see .rar attached

Good luck, Luis