Set analysis max date in month

[bismart]

Want to create report on following sample data.

Table should have 4 lines with value (identified by red colour)

Identify max date in month and report associated value.

Can anyone suggest Set Analysis expression please

Year	Month	Date	DateNum	Value
2011	1	01/01/2011	40544	1
2011	1	15/01/2011	40558	15
2011	2	06/02/2011	40580	6
2011	2	22/02/2011	40596	22
2011	3	09/03/2011	40611	9
2011	3	15/03/2011	40617	15
2011	6	14/06/2011	40708	14

[Oleg_Troyansky · Accepted solution]

You don't need Set Analysis for this problem... Instead, use function called FirstSortedValue:

FirstSortedValue(Value, Date*-1),

assuming that Year and Month are your Chart Dimensions. Multiplying Date by -1 turns sorting from Ascending to Descending.

[bismart]

Thank you

[Anonymous]

worked perfectly - many thanks!

[#5]

Hi Oleg,

          Suppose i have two values in same date then what output i will get ?

Regards,

Antony.

[#6]

I have a similar problem, but i would like extract more information... I will attach us a example:

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!