Table should only show the last action (based on date) from each person

[#1]

Hello,

maybe anyone could help me out with this. I have the following data:

Name	Date	Action
Max	01.10.2014	Drive
Moritz	02.10.2014	Sleep
Moritz	15.10.2014	Drive
Karl	01.09.2014	Drive
Karl	01.10.2014	Take a shower
Julia	01.08.2014	Holidays
Julia	10.10.2014	In the park
Susanne	01.07.2014	Eating
Susanne	05.11.2014	Birthday
Susanne	06.11.2014	Drive
Peter	01.06.2014	Buy a car

No I want to show in a table only the last action from each person. So the goal is this table:

Name	Date	Action
Max	01.10.2014	Drive
Moritz	15.10.2014	Drive
Karl	01.10.2014	Take a shower
Julia	10.10.2014	In the park
Susanne	06.11.2014	Drive
Peter	01.06.2014	Buy a car

Could anybody help me out with this?

Would be so great! Thank you for every help.

[jonathandienst]

Hi

Use this in a straight table with Name as a dimension:

=FirstSortedValue(Action, -Date)

HTH

Jonathan

[jagan]

Hi,

Try like this in script

LOAD

*,

IF(Previous(Name) <> Name, 1, 0) AS LastEventFlag;

LOAD

*

FROM DataSource

ORDER Name, Date Desc;

Now in chart try this

Dimension: Name

Expression: Only({<LastEventFlag={1}>} Action)

Hope this helps you.

Regards,

Jagan.

[#4]

Thank you Jonathan, you helped me so much!

[jagan]

Hi,

If you have two actions on the same date then FirstSortedValue() gives you null.  Check this scenario.

Regards,

Jagan.

[#6]

Hello Jagan,

oh... do you have any solution for this? (not script editor)

Thanks...

Edit: It turns out, that it does not work. Sometimes the first action is shown, sometimes more than one...

[jagan]

Hi,

Try to convert the date format, may be date format is causing the issue, check whether Today() and you date are in the same format.

Regards,

Jagan.

[#8]

They have the same format

[jagan]

HI,

Check this script

Temp:

LOAD

Name,

Date(Date#(Date, 'DD.MM.YYYY')) AS Date,

Action

INLINE [

Name, Date, Action

Max, 01.10.2014, Drive

Moritz, 02.10.2014, Sleep

Moritz, 15.10.2014, Drive

Karl, 01.09.2014, Drive

Karl, 01.10.2014, Take a shower

Julia, 01.08.2014, Holidays

Julia, 10.10.2014, In the park

Susanne, 01.07.2014, Eating

Susanne, 05.11.2014, Birthday

Susanne, 06.11.2014, Drive

Peter, 01.06.2014, Buy a car

];

Data:

LOAD

*,

IF(Previous(Name) <> Name, 1, 0) AS LastEventFlag;

LOAD

*

Resident Temp

ORDER BY Name, Date Desc;

DROP TABLE Temp;

Now in chart try this

Dimension: Name

Expression: Only({<LastEventFlag={1}>} Action)

Hope this helps you.

Regards,

Jagan.