Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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.
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.
Thank you
worked perfectly - many thanks!
Hi Oleg,
Suppose i have two values in same date then what output i will get ?
Regards,
Antony.
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 |
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!