CSV advanced filtering with LO Calc

[Vico]

Hi!

I have a CSV file with (supposely) all my Last.FM scrobbles exported using Last.fm to csv (Github) and i want to know if is possible with LO Calc to make a top-20 (20 more played/repeated songs/rows) in a given year (2020, 2019…).

For reference, the CSV have the following fields:

Artist, Album, Song title, Datetime in d M Y H:i
Marianas Trench,Phantoms,Eleonora,07 Dec 2020 15:48
Backstreet Boys,Never Gone,I Still...,07 Dec 2020 15:44
The Corrs,Forgiven Not Forgotten,Runaway,06 Dec 2020 22:23
...

[erAck]

So the import ends up as a table with Artist in A, Album in B, Song in C, Datetime in D (caveat, to be recognized as date, in the import dialog set an English (best UK) locale and the column type to Date (DMY) if the date order of the selected locale is different (e.g. in an en-US locale)).

1. In column E add a year helper, like in E1 enter Year and in E2 enter =YEAR(D2) and then fill that formula down.
2. Place the cell cursor somewhere on the data and invoke menu Data → Pivot Table → Insert or Edit…, the data range should become selected and in the dialog choose Current selection, OK.
3. In the then Pivot Table Layout dialog drag the fields Year Artist Album Song title (in that order) from Available Fields into Row Fields.
4. Then drag Song title also into Data Fields that then displays Sum - Song title.
5. Double click that and from the Function list choose Count.
6. Click OK.

A new sheet gets added with the pivot table of in which year songs occur, with counts and a total. You can filter each by clicking on the header arrows, or rearrange columns and thus order of evaluation by clicking or dragging headers. Right click somewhere on the pivot table to redefine properties. For an alternative listing with years in columns drag Year into Column Fields instead of Row Fields. Experiment…

See also pivot table online help.